[SQL Server] Database log file autogrow causes connection Timeout analysis

1 , phenomenon, problem description

Customer reflects that a client can not login, the client program log display "Connection database timeout"; Check the corresponding database server, the log shows "autogrow of file" a database log file ' in the database ' a DB ' is cancelled by user or T  Imed out after 2391 milliseconds. Use ALTER DATABASE to set a smaller filegrowth value for this file or to explicitly set a new file size. "

2 , critical process Analysis

Depending on the database log, the database transaction log file is full and the connection session has been actively timed out when it grows automatically.

1. SQL Server needs to keep the following types of logs

A) All log records that have not been "checkpoint"; (the database defaults to about 1 minutes to do a checkpoint)

b) All the log records generated by the non-committed transactions, and all the log records after them (the operations that occupy a large space in the log are: Database recovery based on transaction logs; creating/rebuilding indexes; manipulating large amounts of data; The program opens the cursor without taking the data);

c) All log records to be backed up; (not "simple" mode, logs are considered to be backed up)

D) There are other database function modules that need to read the log. (Transactional replication and database mirroring, where all logs are retained before they are read);

2. When the transaction log file is automatically growing, it will need to "place 0" of the newly added space (the data file is not required by default), resulting in a large number of latches and slow response times. (Set a "timeout exit" of the client program, the general will not be able to connect normally, the database program is not set by default "timeout", so it is generally patiently waiting for log file growth to complete. )

3 , conclusions, solutions and results

Conclusion: Before the problem occurs, the transaction log file has reached 25G, and when the game program connects, it waits for a timeout when it automatically adds 10% space (2.5G).

The workarounds are:

Periodically empty the transaction log that does not need to be backed up (it is recommended to empty the log before full backup/differential backup is performed);

BACKUP LOG database name with with NO_LOG

or set the database option to "Simple Mode" (SQL 2008 only supports this method and does not support manual emptying of the log)

4 , experience Summary, precautionary measures and normative advice

The automatic growth of the database, the proposed set to "**MB", reduce the use of "**%";

For databases that do not require backup logs, empty the log before backing up the database, or periodically empty it;

The weekly "Log growth size" monitoring, changed to daily "Log current size and usage" monitoring, Over ("5GB and 80% alarm");

5 , Notes

Reference http://support.microsoft.com/default.aspx?scid=kb;en-us;2091024&sd=rss&spid=2855

Http://msdn.microsoft.com/en-us/library/ms175935.aspx

[SQL Server] Database log file autogrow causes connection Timeout analysis