Sql-server – Transaction log growing on a mirrored principal

mirroringsql serversql-server-2008sql-server-2008-r2

I have a pair of SQL Server 2008 R2 servers hosting a synchronously mirrored database with no witness. This weekend, we had to do some network maintenance so I paused the mirroring. Upon resuming, the principal's status reverted to the usual Principal, Synchronized. The mirror, however, now reads Mirror, Synchronized / Restoring… I don't recall the Restoring… part usually being there.

The transaction log has begun to grow on the principal, as well, despite hourly log backups. This suggests to me that the synchronization process isn't freeing up transaction log space.

What's going on here and how do I fix it?

Best Answer

Mirror, Synchronized / Restoring... is the typical state for the mirror instance.

You can determine why the log file is growing by issuing the following command in a SQL Server Management Studio query window:

SELECT name, LOG_REUSE_WAIT_DESC 
FROM sys.databases 
WHERE name='your_database_here';

This will show several possible choices, such as:

NOTHING
CHECKPOINT
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
OTHER_TRANSIENT

You may need to perform at least one transaction log backup, among other actions depending on the LOG_REUSE_WAIT_DESCRIPTION shown in the query above. For instance, there may be a long-running transaction that is forcing the log files to not be re-used.

Related Question