SQL Server 2005 unable to shrink the transaction log, help? (peculiar issue)
On one of the archive database (using SQL 2005) we have a monthly task to shrink the transaction log during every 1st week of the month, this is required due to clear up the space on one of the drives where the additional transaction log file located (its a long story that we cannot replace the hardware, as they are legacy components associated on this server for this database application).
Coming to the issue, we have had this problem since last 3 months that transaction log size is not 'REDUCED' to the desired level. The relevant call has been escalated to me for a resolution, as 1st & 2nd level DBAs are unable to resolve it within this period. My first assumption was to check for any uncommited or open transactions using DBCC OPENTRAN, there are no open transactions and infact the database is in READONLY mode which is a default mode due to a requirement for that application.
I have had lookup of complete transactions and processes against this database, apart from my connection there were no connections applied. I was absoulutely puzzled to see this behaviour and at the same time reluctant to raise a support call for this 'small' problem. As fellow DBAs were referring that this was never a problem before and not on other SQL 2005 instances though the database & applications are different to this database specifically. So my tracks are going back until November 2006 to see what kind of changes have been applied on this Server, as we maintain a documentation for all the production changes even though if it is a deletion of temporary file on that server due to the high-visibility & availability requirement for this database. Apart from Service Pack2 patch there were not any changes to the database or server components. As mentioned before few other servers do have similar levels of service pack patches. Even I have gone through this KBA that talks about transaction log issue when you are using FullText, which is not a case here. I have had lookup through the list of bugs fixed in Service Pack 2 as per this KBA link, none of them are applicable in this case.
Back to old drawing board, for a peace of mind I have opened up the BOL (February 2007 update) to refer the changes on transaction log architecture and lookup on few system tables within the master database. Few things I was able to notice to see difference to the previous versions & service pack level that the columns in sys.sysdatabases table: log_reuse_wait & log_reuse_wait_desc.
As per BOL entry on sys.sysdatabases log_reuse_wait column is: Reuse of transaction log space is currently 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 = Other (transient)
Also refer to Factors on log records active information.
I could see there is a frequent change in this column against that database when the application is performing active connections. The value was fluctuating between 1 and 4, that means due the recovery model on this database the checkpoint is issued when active transaction is under progress. The monthly job to shrink the transaction log for this database has taken more than required time since last 2 months and at the same time I was referring through an excellent information by Tibor Karaszi on DontShrink article.
Phew, by executing the SHRINK operation job twice we are able to get back the relevant results on the disk space.