Large Transaction Log with Mirrored Db

  • I have database mirroring in operation under SQL2005 but the transaction log for the principal database is extremely large, much more than the actual db size. What is the best way of reducing this size? I would truncate it but am not sure what effect this would have on the mirrored database as obviously don't want to mess up the synchronization. Also in order to prevent this happening again, should daily transaction log backups be taken?

    Thanks in advance for your help.

  • Do you currently have periodic transaction log backups running on the principle database? If not, you need to start doing so. The transaction log is not truncated by the mirroring or full/differnetial database backups. Transaction log backups will truncate the log of commited transactions. This will not release space back to the OS or shrink the physical size of the transaction log file. You will need to do this manually after you get the transaction log backups started.

    😎

  • yes, mirroring does not eliminate the need for transaction log backups. You need to have those setup, possibly more often than once a day.

  • [font="Verdana"]The same thing happens in my case and finally I've sorted out that Mirroring Endpoint is in disabled state and hence I've enabled the same and the transactions from the principal is flowed to the mirror server. After that log backup was taken and truncated. Yes its better to take frequent log backups to avoid the same in future![/font]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply