SQL Server log files

process: yesterday afternoon the database ran, the performance is not even on the database, restart service after the good.

Query Japanese files, "autogrow of file ' Xx_log ' in database ' XX ' is cancelled by the user or timed out after 1896 milliseconds. Use ALTER DATABASE to set a smaller filegrowth value for this file or to explicitly set a new file size.

Find reason: The database is growing automatically, so the connection times out.

Analysis: The database log file is full and starts to grow automatically, automatically growing by default of 10%. There was no problem at this early stage, but it was late. The log file is huge, there are dozens of G, dozens of g of 10% also have a few g, so go to disk and then allocate so much space, it is easy to timeout.

Workaround: 1, the automatic growth mode is set to fixed, too small, easy to cause disk fragmentation, too big easy to time out. The general 500M is no problem.

2. Periodically shrink the log files.

Other: SQL Server database files have MDF files and LDF files. MDF is a data file, and LDF is a log file. If you have a complete log file, you can restore the database to a state at any point in time. The importance of the log file is visible, but the log file generally grows fast, so you need to shrink periodically.

 Use[Master]  GO  ALTER DATABASEDatabase nameSETRECOVERY Simple withno_waitGO  ALTER DATABASEDatabase nameSETRECOVERY Simple--Simple ModeGO   UseRkGO  DBCCShrinkfile (N'Database name _log',2, truncateonly)--after setting the compressed log size to 2M, you can specify your ownGO   Use[Master]  GO  ALTER DATABASEDatabase nameSETRECOVERY Full  withno_waitGO  ALTER DATABASEDatabase nameSETRECOVERY Full  --revert to Full modeGO  

Can be divided into three steps, the data table is set to Simple mode, contraction, and then set back to full mode.

Database has three modes, simple recovery model, full recovery model, bulk-Logged recovery model

, you can see here https://www.cnblogs.com/OpenCoder/p/5708226.html

SQL Server log files