Log files Growing

Original: Log file growing

First understand the logging that SQL Server needs to save:

1. All log records that have not been "checkpoint":

SQL Server timed Execution (Checkpoint) to ensure that "dirty pages" are written to the hard disk. Did not do checkpoint, may be only in memory changes, data files are not synchronized. SQL Server is logged in the log file of the hard disk and is re-modified after an abnormal restart.

2. The logs generated by all uncommitted transactions and their subsequent log records:

All logs are in strict order and cannot be skipped.

3, to do the backup of the log records:

If the recovery model is not a simple mode, SQL Server will assume that the user is going to back up the log records. All records that are not backed up will be retained.

4. There are other database functions that need to read the log:

such as transactional replication (transactional Replication) and mirroring.

In addition to the above types, other types will be truncated at checkpoint when the space mark occupied is reusable. If the reuse space is sufficient, the log space is not reported to be full. The frequency of the checkpoint is determined by the server's "Recovery Interval", which defaults to about one minute.

There are usually reasons for the growing log:

1, the database recovery model is not simple, but did not do log backup:

In this mode, a full backup and a differential backup do not truncate the log.

2. There is a transaction on the database that has not been committed for a long time:

SQL Server does not interfere with the behavior of the front end program's connection legacy transactions in SQL Server. As long as you do not exit, the transaction will persist until ( here is known, thanks to Kalagooooo's careful discovery, now to correct) the front-end actively commit or rollback. It is useless to do a log backup at this time.

3. There is a large transaction running on the database:

such as establishing and rebuilding indexes. Or insert/delete large amounts of data. or the server-side cursor does not take the data out in time.

4, database replication or mirror out of the exception

Avoid these phenomena to prevent the log from growing. For databases that do not log backups, set it to Simple mode. In the case of full mode, be sure to do regular log backups. If the image or copy in addition to the problem, to be processed in a timely manner, if not processed, then temporarily remove the copy or mirror. When designing a program, you should also avoid too long and too much time in the transaction.

For log growth processing:

Step 1: Check the log now usage and database status:

Check the log usage percentage, recovery model, and log reuse wait status. Since 2005, Sys.databases has joined Log_reuse_wait (LOG_REUSE_WAIT_DESC) to reflect the reason for the inability to stage logs

Log_reuse_wait	Log_reuse_wait_desc	Description
0	Nothing	There are reusable virtual log files
1	CHECKPOINT	The checkpoint did not appear after the last log truncation, or the log header has not moved across a virtual log file (all modes)
2	Log_backup	Requires a log backup to move the log table head (non-simple mode). After the log backup is complete, the log header is moved forward, and some space may become reusable.
3	Active_backup_or_restore	Database backup or Restore in progress (all modes)
4	Active_transaction	Transaction is active (all modes)
5	Database_mirroring	Database mirroring lag (full mode)
6	REPLICATION	In transactional replication, publication-related transactions are still not delivered to the distribution database (full mode only)
7	Database_snapshot_createion	Creating database Snapshot (all modes)
8	Log_scan	Log scan in progress (all modes)
9	Other_transient	This value is not currently used

Use the following script to check:

DBCC SQLPERF (logspace) goselect Name,recovery_model_desc,log_reuse_wait,log_reuse_wait_descfrom Sys.databasesGO

If Log Space used (%) is high, locate it immediately and why it cannot be cleared. If the status is:log_backup, it means ( here originally thought, thanks to the careful discovery of kalagooooo, now to correct come over ) SQL Server, means that SQL Server waits for a log backup. To check if a log backup is required.

Step 2: Check for the longest active transaction:

If most of the logs are in use and the reuse status is:active_transaction, look for the longest transaction who applied:

DBCC opentrangoselect  *from    sys.dm_exec_sessions as T2,        sys.dm_exec_connections as T1 cross        APPLY Sys.dm_exec_sql_text (T1.most_recent_sql_handle) as stwhere   t1.session_id = t2.session_id and        t1.session_id > 50

return after execution:

After finding the longest transaction, check for problems first, or if there is a problem, it is best to commit or rollback the transaction from the application level. If not, use the kill SPID to kill the process.

Log files Growing