Some situations can prevent the Transaction Log from being truncated
Generally, if your database transaction log is growing out of proportion then you will need to back it up more regularly. This assumes you are using the Full Recovery model and you back up the log in the first place. However, even if you backup the log on a regular basis there may be reasons that the log continues to grow. Let’s take a look. Remember, only the BACKUP LOG statement will truncate the transaction log. The BACKUP DATABASE statement does not. This is a common misconception to DBA’s new to SQL Server. Truncation means discarding transactions that have been backed up, to free up space within the Transaction Log. Truncation does not make the .LDF file smaller. You will need DBCC SHRINKFILE for that after the backup. However, even if you backup the log on a regular basis there may be reasons that the log continues to grow. 1. Active Transaction: A single long running transaction can limit a truncation operation. SQL Server cannot truncate beyond the start of the oldest currently active transaction. A long running transaction may be blocked because of locks. It may be a BULK INSERT that is not split up into batches using BATCHSIZE. It may be due to an application design flaw, where an application starts a transaction then opens a window to the user during the transaction. The user leaves the window open and voila – a long running transaction. To find out the oldest active transaction use DBCC OPENTRAN. 2. Replication: During transactional replication, transactions that are still undelivered to the distribution database will limit truncation. This has the effect of a long running transaction even though the transaction may have committed already. Or maybe you have the Log Reader Agent running to a schedule and that interval is too long. Again, to display the oldest non-distributed replicated transaction, use DBCC OPENTRAN. 3. Database Mirroring: Database mirroring has been paused, or under high-performance mode, the mirror database is significantly behind the principal database. This means that updates that are due to be applied to the mirror database are lagging and cannot be truncated. In this case, you may have to stop database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database (using WITH NORECOVERY), and restart mirroring but that would be a last resort. You can find out exactly what is causing the truncation issue using sys.databases and a column called log_reuse_wait_desc . Here’s a good technical article on the subject. Factors That Can Delay Log Truncation: http://msdn.microsoft.com/en-us/library/ms345414.aspx Cheers Brian
