This is a followup question to a previous question about why I couldn't shrink the log file on the principal database.
To make a long story short, I setup database mirroring but forgot to make sure the job that backed up the transaction log was running again, and the transaction log grew to almost 60GB.
Since mirroring was set up, this size increase was duplicated on the mirrored server, and eventually took up all the disk space and made the mirror database unusable.
Per this question about transaction log maintenance on a mirror database, you can't back up the log on the mirror, but when asked specifically in comments about how to shrink an overgrown log file on a mirror database, a comment was left that
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.
This seems to suggest that there might be other ways to shrink the log file on a mirror, and this method might not necessarily be safe to do on a production server.
Is there a way to safely shrink the transaction log file on a database mirror?
Best Answer
The
DBCC SHRINKFILE
command will be mirrored from the principal to the mirrored database. Here's some proof.Create a sample database on the principal:
Create the same database from a backup with
NORECOVERY
:Setup your mirroring session however which way you choose.
On the principal database look at the database file sizes:
My result set looks like this following:
On the mirror database, create a snapshot and look at the same information:
My result set looks like the following:
Now grow the transaction log file on the principal database (I brought it to 1 GB):
Looking at the principal database's transaction log size, we now see the adjusted size:
My result set looks like the following:
Create another snapshot on the mirrored database, and look at the transaction log file size there:
My result set looks like the following:
Now do the
DBCC SHRINKFILE
on the principal:My result set is the following:
Create a third and final snapshot on the mirrored database, and look at the size:
And I get the following result set:
So as you can see here, the
DBCC SHRINKFILE
command is in fact mirrored to the mirror database.