Knowledge Base

Transaction log continually growing on disaster recovery database

Category: Troubleshooting & error messages
Date: 30 Jan 2009
Product: SQL Backup
Versions: all

It is a possibility that the transaction log (.ldf) of the disaster recovery (DR) database updated by log shipping will keep growing with every restore.

As people familiar with Log Shipping and loading state databases will know, SQL Server will not allow you to backup the database or truncate the transaction log while in the "loading" state, nor can you shrink the log file. If you bring the database online in order to truncate the log, you then cannot return it to a loading state and continue Log Shipping.

The question is why would a log file grow to a very large, and what can you do to fix the problem if it arises?

The log file of the DR database should never grow to a size larger than the largest log file that it needs to restore. It will then always reclaim the space in the log for subsequent restores, unless it receives a log file larger than the current size, then it will autogrow to allow the restore.

A DR database log file will keep growing if SQL Server cannot recover the space in the transaction log, and then has to autogrow to accommodate the next log file. There could be a number of reasons why SQL Server is unable to free the space, but they all translate to a corrupt transaction log which cannot be fixed. For example, there could have been a transaction that didn't commit properly to the DR database, but wasn't enough of an issue to cause SQL Server to report an error. This means that the transactions logs can still be restored, but the log space cannot be recovered.

As the restores are still completing, the only problem is the growing size of the log file. The actual data in the database will be consistent with the source, so if it needs to be brought online in a DR situation, there will be no problems with the database.

If you experience that the log file is growing with every restore, then the only resolution will be to reseed the database from the primary location.