10

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

2 Answers 2

5

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?

You can't do a log backup on the mirror database.

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?

You can't do a full database backup on the mirror database either.

Take this for instance: I have Server1 which houses the principal database AdventureWorks2012, and I have Server2 which contains the mirror. Here's what happens when I try to run backups on the mirror database (on Server2):

use master;
go

backup database AdventureWorks2012
to disk = 'c:\sqlserver\AW_mirror.bak';
go

Msg 954, Level 14, State 1, Line 2
The database "AdventureWorks2012" cannot be opened. It is acting as a mirror database.
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.

backup log AdventureWorks2012
to disk = 'c:\sqlserver\AW_mirror.trn';
go

Msg 954, Level 14, State 1, Line 1
The database "AdventureWorks2012" cannot be opened. It is acting as a mirror database.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

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:

When you backup the log on the principal, the virtual log files (individual units within the log file) are marked as re-writable. The same VLF’s are marked as re-writable in the mirror log file as well. The VLF status is mirrored on the database.

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.

3
  • I hate it when there is an article out there that answers my exact question and I don't find it. This makes perfect sense, when I took over the logs weren't being backed up and grew very large on the principal so after I started regular backups I shrank the logs but didn't think to do this on the mirror. Thanks! Feb 22, 2013 at 20:33
  • 1
    An addendum question would be is it possible to shrink the overgrown mirrored transaction log now that we have regular transaction log backups on the principal? Feb 22, 2013 at 20:40
  • @JeremieGrund One way to do this would be to failover to the mirrored database and do the shrink there. Thoroughly test this out in a non-production environment to ensure it has the behavior you want/expect. Feb 22, 2013 at 22:24
0

@JeremieGrund - If the database physical file architecture is same( data & log file drive names & locations), then if you shrink the log file on the principal, the shrink command will be sent to mirror and do the same on mirror. So in this way your mirror log file can be maintained.. If the files architecture is not identical then you should follow Thomas Stringer Suggestion..

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.