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

Some good ideas can be found here: http://yukonspace ... transaction-log-growth.

From my experience with full-recovery mode databases (should behave in somewhat similar way) you need to make regular full backups at least, otherwise logs only grow.

Answer 2

I actually do have backups running, but they do not shrink.

Answer 3

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

Answer 4

Are you sure the Mirror did not fall behind? The principal server instance can't truncate the log until they have been successfully applied to the mirror. After a certain point I believe you may have to backup the transaction log and apply it to the mirror and restart mirroring.

Answer 5

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.

Answer 6

http://support.microsoft.com/kb/937531