Transaction Log in SQL Server

Transaction log files are most important in SQL Server database. It records all the transactions and the database modifications made by each transaction that occurs in the database.

Transaction Log Truncation

We should closely monitor the transaction log files growth to prevent it from filling-up. Periodical truncation of transaction log files is useful for freeing used space in log files by deleting inactive virtual log files from logical transaction log of the SQL Server database. SQL Server also automatically truncates the transaction log files on below event.

1.   After check point occurs, under the simple recovery model.

2.   If checkpoint has occurred since the previous backup, then truncation occurs after the transaction log backup, under the Full or Bulk-logged recovery model. Otherwise it is copy-only transaction log backup.

SQL Server cannot truncate the transaction log records as long as it remains active. We can identify the reason for the transaction log truncation delays from the below query.

SELECT

            name,

            log_reuse_wait,

            log_reuse_wait_desc

FROM

            sys.databases

Find below the different factors that are defined through log_reuse_wait and log_resue_wait_desc, for the log truncation delays.

0             - NOTHING [Currently there are one or more reusable transaction log files].

1             - CHECKPOINT [No checkpoint has occurred since the last log truncation].

2             - LOG_BACKUP [Log backup is required before the log truncation. It is applicable only for Full or Bul-Logged recovery models].

3             – ACTIVE_BACKUP_OR_RESTORE [Data backup or restore is in progress].

4             – ACTIVE_Transaction [Transaction is still active – it might be because of long running transaction or deferred transaction. Deferred transaction is nothing but a active transaction whose rollback is blocked due to some unavailable resources].

5             – DATABASE_MIRRORING [Database mirroring is paused or mirroring is under high performance mode].

6             – REPLICATION [During transactional replication, publisher’s transaction is still undelivered to the distributor].

7             – DATABASE_SNAPSHOT_CREATION [A database snapshot is being created].

8             – LOG_SCAN [Log scan is occurring].

9             – AVAILABILITY_REPLICA [Secondary replica of availability group is applying transaction log records to its secondary database].

10          – For internal use only.

11          – For internal use only.

12          – For internal use only.

13          – OLDEST_PAGE [If Indirect Checkpoint is configured in a database, the oldest page on the database might be older than the checkpoint LSN. In this case, the oldest page can delay log truncation].

Log truncation does not reduce the size of the physical transaction log file. We should go for database shrink to reduce the physical transaction log file size. 

Importance of Transaction log

1.  It plays vital role during recovery process which may be either individual transaction recovery or point-in-time database recovery.

2.   It supports in high availability solutions such as Database Mirroring, Log shipping, Transaction replication and AlwaysOn Availability group.