How to shrink a SQL Server Log file with Mirroring enabled?

Question

I have several databases for my applications that use SQL Server 2005 mirroring to keep a nice copy of the data somewhere else. Works like a charm, however, the log file just seems to be growing and growing, one is at 15GB for a 3GB database.

Normally, I can just shrink it - however an error pops up that this specifically cannot be done. But, it seems eventually if unchecked would just expand to use all the space on the drive.

I see that I can set a maximum file size for the log file, is that the answer here? Will the log just roll when it hits the max, or will the DB just stop functioning?

Thanks

Answer 1

We ran into the same issue after switching from log shipping to mirroring. You have to create a job that regularly backs up the transaction log (every 15 or 30 minutes or so) to keep the log size from getting out of hand.

If it is already out of hand, run BACKUP LOG TO DISK = 'Nul', then run a DBCC SHRINKFILE command. Then you can get your job set up.

Note that The 'Nul' is not a misspelling, it is an old DOS trick that behaves as if you are writing a file, but really just dumps the information off into the ether so it doesn't take up space on the machine.

Also, your log will grow until you run out of space, then the whole thing stops working. Your app will receive an error that the transaction log is full.

EDIT: David correctly pointed out that this action will break the log chain and reduce the ability to recover from failure. Be sure to use the backup log to 'nul' command as a last resort. If you have space on the drive, you should take a proper log backup and setup your log backup plan. Make sure you also include regular full backups and a cleanup task to remove old files.

Answer 2

I would look at why your logfile is growing, then you can define a strategy for solving your issue. It might be worth checking your mirroring state

When a principal database is exposed, it is active with user connections and processing transactions. However, no log records are being sent to the mirror database, and if the principal should fail, the mirror will not have any of the transactions from the principal from the point the principal entered the exposed state. Also, the principal's transaction log cannot be truncated, so the log file will be growing indefinitely. ref http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx