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.

We setup database mirroring over the weekend, and forgot to re-enable the job that backs up the transaction logs. When I came in this morning, the transaction log had ballooned to 58GB, and was taking up most of the drive space.

I did a manual backup of the transaction log to disk to get the database running again, however running DBCC SHRINKFILE does not appear to decrease the physical size of the transaction log file.

DBCC SHRINKFILE (N'MyDatabaseName_Log', 1000)

If I check out the log usage using

DBCC SQLPERF(LOGSPACE)

I can see that only 22% of the current log is being used

Database Name    Log Size(MB)    Log Space Used (%)    Status
MyDatabaseName   55440.87        22.38189              0

If I check out log_reuse_wait_desc in sys.databses, the only record I see is DATABASE_MIRRORING, so I am guessing the mirror is playing a role in why the physical size of the log file won't shrink?

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = N'MyDatabaseName';

I've also noticed my principal database mirroring state is Suspended, and trying to Resume it immediately fails with the following error:

The remote mirroring partner for database 'MyDatabaseName', encountered error 5149, status 1, severity 25. Database mirroring has been suspended. Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.

The error logs on the mirror server contains this error as well, but also contains errors about the log file drive being full

MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file.

and

F:\Databaselogs\MyDatabaseName_1.ldf: Operating system error 112(There is not enough space on the disk.) encountered.

The principal server has 60GB on the log file drive (there are other databases hosted here), while the mirrored server only has 45GB.

Backing up the log file made the database usable again, however I also want to decrease the size of the physical log file on the disk, and get the mirroring resumed.

How can I shrink the size of my physical transaction log file without compromising the mirroring or the backup chain?

I am running SQL Server 2005

share|improve this question
    
What does the secondary server have in the SQL Server error log? You should have a few error log messages dictating what happened to cause the mirroring session to be suspended. –  Thomas Stringer Apr 22 '13 at 12:50
    
@ThomasStringer I was actually about to post my own answer to this question... I think my answer is that I can't, at least not without disabling the mirror and setting it up again. The space on the mirrored server reserved for log files is less than the space on the principal server (principal hosts other databases), and I have no way of reducing the size of the mirrored transaction log. I've also updated my question with the error messages from the mirrored server though –  Rachel Apr 22 '13 at 13:03

1 Answer 1

From what I can tell, I can't.

I believe much of the log file is waiting to be mirrored to the mirror server, but the mirror is down and can't be resumed because the mirrored transaction log has also grown to take up all the space on the disk.

This theory is further backed up by the fact once I remove the mirroring, the DBCC SHRINKFILE command correctly shrinks the physical log file, and the log_reuse_wait_desc is back to waiting on LOG_BACKUP

I can't shrink the log file on the mirrored server because it's acting as a mirror and can't be opened, so I think the mirror is unrecoverable.

So I am going to delete the mirror entirely, and set everything up again (very slow process with a 300GB database). And this time, I'll make sure the transaction log size is fairly small before starting up the mirror, and that the transaction log backups are running once the mirror is back up and running.

I'm also going to set a limit on how big the transaction log can grow on the production server, so the mirror doesn't ever try to grow its transaction log beyond the size available on the disk.

share|improve this answer
1  
Another option may be to take a diff backup and restore that to the mirror. It may not work but it's worth a try if you or anyone else runs into this again. –  cfradenburg Apr 22 '13 at 14:19
    
@cfradenburg Yes, I did try to do that but it didn't work in my case because of the timing of my backups over the weekend and the disk space getting maxed out on the mirror. Its definitely worth trying before deciding to completely delete the mirror and start over though. –  Rachel Apr 22 '13 at 14:21

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.