6

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?

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.