Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer
    
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! –  Jeremie Grund Feb 22 '13 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? –  Jeremie Grund Feb 22 '13 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. –  Thomas Stringer Feb 22 '13 at 22:24

@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..

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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