June 5, 2008 at 10:03 am
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.
June 5, 2008 at 11:23 am
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.
😎
June 5, 2008 at 11:28 am
yes, mirroring does not eliminate the need for transaction log backups. You need to have those setup, possibly more often than once a day.
June 5, 2008 at 10:14 pm
[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]
Regards..Vidhya Sagar
SQL-Articles
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply