Recently during consulting engagement Comprehensive Database Performance Health Check, my client asked if I knew why they could not shrink the transaction log. Well, I know how one can find the reason for the nonshrinking transaction log, let us learn that in this blog post.
First of all, run the following script
SELECT name, log_reuse_wait_desc
FROM sys.databasesThe above script will return the name of the database along with the log_reuse_wait_desc. The column displays the reason transaction log space is currently waiting to clear.
Shrink the Transaction Log
There can be numerous reasons why one cannot shrink the log file. You may see any of the following values as a reason for your database.
- NOTHING
- CHECKPOINT
- LOG_BACKUP
- ACTIVE_BACKUP_OR_RESTORE
- ACTIVE_TRANSACTION
- DATABASE_MIRRORING
- REPLICATION
- DATABASE_SNAPSHOT_CREATION
- LOG_SCAN
- AVAILABILITY_REPLICA
- OLDEST_PAGE
- XTP_CHECKPOINT
- SLOG_SCAN
If you see value NOTHING as your reason, then you do not have to worry as you will be able to shrink the log file successfully. However, if you see any other reason, you should read this official documentation from Microsoft, which explains why you cannot shrink your log file. Once you know the reason for not being able to shrink the database, you can find the solution.
If due to any reason, you are not able to find the solution, you can always reach out to me via Twitter.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
