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.

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?

share|improve this question
2  
The only supported way is to shrink the file on the principal which will also shrink the file on the mirror. If your mirror is no longer the same as the principal, you really do not want to attempt to failover to the mirror. Break mirroring, and recreate it, as you said in your previous question. –  Max Vernon Apr 22 '13 at 14:29
add comment

1 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 database MirroredDb;
go

Create the same database from a backup with NORECOVERY:

restore database MirroredDb
from disk = '\\backupdir\MirroredDb.bak'
with norecovery;
go

Setup your mirroring session however which way you choose.

On the principal database look at the database file sizes:

use MirroredDb;
go

select
    name,
    size
from sys.database_files;

My result set looks like this following:

name            size
MirroredDb      392
MirroredDb_log  104

On the mirror database, create a snapshot and look at the same information:

create database MirroredDbss
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb.ss'
)as snapshot of MirroredDb;

use MirroredDbss;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  104

Now grow the transaction log file on the principal database (I brought it to 1 GB):

alter database MirroredDb
modify file
(
    name = MirroredDb_log,
    size = 1GB
);
go

Looking at the principal database's transaction log size, we now see the adjusted size:

use MirroredDb;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  131072

Create another snapshot on the mirrored database, and look at the transaction log file size there:

create database MirroredDbss2
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb2.ss'
)as snapshot of MirroredDb;

use MirroredDbss2;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  131072

Now do the DBCC SHRINKFILE on the principal:

use MirroredDb;
go

dbcc shrinkfile('MirroredDb_log', 0);
go

select
    name,
    size
from sys.database_files;

My result set is the following:

name            size
MirroredDb      392
MirroredDb_log  104

Create a third and final snapshot on the mirrored database, and look at the size:

create database MirroredDbss3
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb3.ss'
)as snapshot of MirroredDb;

use MirroredDbss3;
go

select
    name,
    size
from sys.database_files;

And I get the following result set:

name            size
MirroredDb      392
MirroredDb_log  104

So as you can see here, the DBCC SHRINKFILE command is in fact mirrored to the mirror database.

share|improve this answer
    
Thank you I didn't realize the size of the log file was copied from the principal server as well. But in the event that the mirror isn't functional due to the transaction log size, is the only solution to delete and recreate the mirror? There's no way to safely shrink the mirror log when the mirroring process isn't functional? –  Rachel Apr 22 '13 at 14:51
    
You can't connect to the mirrored database, therefore you can't run the DBCC SHRINKFILE on it. As for if you can fix your mirror session without removing mirroring on that database, there's a little more investigation that would have to take place there. It seems like it was suspended because of the file size. Have you tried resuming the partner? –  Thomas Stringer Apr 22 '13 at 15:11
    
Yes, but it immediately went back to being suspended. I am presuming it is because too much time has passed since the mirroring stopped, so a lot of data still needs to be transferred before it gets to the "shrink log file" part. I was hoping there was some other way that I just wasn't aware of, such as something I could run from the mirror database to shrink the file, or some file system manipulation, but that doesn't appear to be the case. Your answer is still very helpful though, so thank you :) –  Rachel Apr 22 '13 at 15:26
    
Hope no longer. Once the database gets suspended/disconnected and a log backup gets executed on the primary you are pretty much hosed on getting the mirror back in synch without a re-init. –  Jason Cumberland Apr 23 '13 at 21:23
add comment

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.