Friday 19 April 2013

Unable to shrink the transaction log of SQL Server database

It is possible that even after trying to shrink the transaction log of a database, the ldf file size remains the same even if there is a lot of available free space in the log.
Because the active part of the log cannot be truncated or removed by shrinking, truncation can be delayed when log records remain active for a long time.
To see what is the log_reuse_wait for the transaction logs of databases run this query:
select name, database_id, log_reuse_wait, log_reuse_wait_desc from sys.databases

Reuse of transaction log space can be waiting on one of the following:
0 = Nothing
1 = Checkpoint
2 = Log backup
3 = Active backup or restore
4 = Active transaction
5 = Database mirroring
6 = Replication
7 = Database snapshot creation
8 = Log Scan
9 = An AlwaysOn Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database.
10 = For internal use only
11 = For internal use only
12 = For internal use only
13 = Oldest page
14 = Other (transient)
SQL Server 2008 R2:
http://msdn.microsoft.com/en-us/library/ms345414%28v=sql.105%29.aspx
SQL Server 2012:
http://msdn.microsoft.com/en-us/library/ms178534.aspx

No comments:

Post a Comment