SQL Server Version: 2008 R2 Enterprise SP2
I am trying to get a handle on our SQL Server maintenance and I came across something I think is incorrect. We have a single production instance with 3 databases each being mirrored offsite to a DR instance.
While looking at the DR instance I noticed that the LDF files were huge, over 35GB for the heavily used databases.
I understand that this is probably due to the mirror databases being in Full recovery mode and that the logs have never been backed up, they will just continue to grow until running out of drive space.
We are doing log backups on the principal database, and my question is what are the gotchas with doing a log backup on a mirror?
At least one full database backup from the mirror has to be completed before doing a log backup, in that case are there special options that need to be used because it is a mirror?
Again this is transaction log maintenance recommendations on the MIRROR database.
Thanks for any input
Best Answer
You can't do a log backup on the mirror database.
You can't do a full database backup on the mirror database either.
Take this for instance: I have
Server1
which houses the principal databaseAdventureWorks2012
, and I haveServer2
which contains the mirror. Here's what happens when I try to run backups on the mirror database (onServer2
):Take a look at this Database Mirroring FAQ by Robert Davis. I will quote him regarding this operation and the mirror database transaction log maintenance:
So there you have it. If you have transaction log backups on the principal then similar log reuse behavior will be mirrored to its partner database.