Transaction Log maintenance on Mirror database

Question

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

Answer 1

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.

Answer 2

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