Trying to figure-out why your log file for a particular database is not “naturally” shrinking is the first steps you need to take in order to diagnose and resolve a large SQL Server transaction log file.

Transaction Log files generally grow because there is something that is stopping SQL Server from reusing the already allocated space to this file, which will essentially cause the Transaction Log file to grow (as per the growth configuration settings) in order to keep the database operational. This could be caused by a natural increase in utilization for that database, or by a blockage (an active operation running too long, or waiting on mirroring to complete), so before deciding how to proceed with resolving a large Transaction Log file, it is highly recommended to figure out why did this happen.

In order to figure out what exactly is causing a transaction log file for a particular database to not be reused, the sys.databases dynamic management view could be used, particularly the log_reuse_wait_desc column which gives a text based description of the blockage in reusing the transaction log. The DMV can be queries as such

For each database, this query will return what (if any) is the cause of the wait in reusing the transaction log file. Values for the log_reuse_wait_desc column can be (important waits are highlighted in bold):

• NOTHING: The transaction log file can be reused, nothing is blocking reuse.
• CHECKPOINT: A checkpoint has not been reached yet, you can force a checkpoint using  the SQL Server CHECKPOINT command.
• LOG_BACKUP: Self explanatory, this means that the database is in FULL recovery mode and a log backup has not been issued yet, it is important to note that you might need 2 log backups before space can be reused.
• ACTIVE_BACKUP_OR_RESTORE: If there is a backup or a restore operation that is currently running, SQL Server will be unable to reuse the log file until this operation is completed.
• ACTIVE_TRANSACTION: There is a transaction running on the database, hence SQL Server is unable to reuse the log file. You can identify the transaction in question using Activity Monitor or other DMVs
• DATABASE_MIRRORING: If the database is involved in a mirroring session which has not completed moving all the data across between participants.
• REPLICATION: Same as mirroring but with replication.
• DATABASE_SNAPSHOT_CREATION: When a database snapshot is being created logs cannot be reused until this operation has completed
• LOG_SCAN: This should be a brief routine operation and should be done quickly.
• AVAILABILITY_REPLICA: delay in log reuse is caused by an availability replica that belongs to an AlwaysOn availability group, if this is encountered regularly then you might want to check your AlwaysOn connectivity between nodes.
• OLDEST_PAGE
• OTHER_TRANSIENT

It is important to note that removing whatever is blocking your transaction log file from being reused does not mean that the transaction log file will shrink, once a transaction log file has grown (and unless auto-shrink has been setup or a shrink command has been explicitly called) the transaction log file will not shrink in size in the file system, instead the newly acquired space will be reused.

I personally like to monitor any database growth activity (using the default trace file), and send an alert when an unexpected growth operation has taken place, this is to help me correctly configure my growth parameters as well as detect any abnormal growth activity on my system.

Why is my SQL Server Transaction Log not Shrinking (or Reusing the Log file)? rated 4 out of 5 by 1 readers