One thing you need to be careful about with SQL Server 2005/2008 Database Mirroring is making sure that you don’t exceed the I/O capacity of the storage subsystem of the SQL Server instance where the mirrored database lives. If you do this with High Safety mode (synchronous) mirroring, your applications will be slowed down, waiting on the mirror.

This usually happens when you do something that generates an unusual amount of log activity (such as index creation or maintenance, or shrinking a data file). In this case, I needed to shrink a data file after some large data deletions, to get ready for a server consolidation. Don’t worry, I know that shrinking data files “is bad”, but I really needed to do it here. Since I am using High Performance mode (asynchronous) mirroring, the application was unaffected by this (from a performance perspective).

The Principal copy of this database has 216 spindles of I/O, while the Mirror copy only has 45 spindles of I/O. The work got done on the Principal, was sent over the network to the Mirror, where the Unrestored log grew as it was waiting to be restored. SQL Server Management Studio does not tell you anything about this, even if you refresh the database in Object Explorer, since it considers the databases to still be synchronized. If you query sys.databases, you will detect the problem.

-- Check log reuse description for User databases
SELECT name, log_reuse_wait_desc 
FROM sys.databases
WHERE database_id > 4;

This query gives a result like this if Database Mirroring is preventing the transaction log from being internally truncated.

name           log_reuse_wait_desc
ngfulltext1    DATABASE_MIRRORING

The Database Mirroring Monitor makes the situation a little more clear. Here, we see the Unrestored log size at about 4.6GB, with an estimated restore time of 15:22. This means that based on the the current restore rate, the tool thinks it will take 15:22 to get caught up. You can refresh the view periodically, and see the numbers jump around.

When this happens, I generally watch the monitor for a while to confirm that we are catching up, and if necessary, cancel any currently running maintenance jobs to lessen the amount of new activity. 

If that unrestored log continues to grow, you could have a big problem, since your transaction log will eventually fill up and have to be grown to match. Worst case scenario is that you run out of disk space on your log drive. As an emergency stopgap, you could add a second transaction log file on another drive., (which you would want to remove later). The ultimate fail-safe in this situation is to remove the mirroring  from that database, which will immediately resolve the issue. Then, you will have to reinitialize the mirror.

The moral of this story is to try to make sure you have comparable I/O capacity for your mirror, and to be careful not to do things that generate too much log activity at one time.