8

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

2
  • 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. Apr 22, 2013 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, 2013 at 13:03

2 Answers 2

2

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.

2
  • 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. Apr 22, 2013 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, 2013 at 14:21
1

Ran into the same issue today with our DBs, which for some reason had stopped backing up the transaction logs for the last 3 months and logs had balooned to 200GBs all up.

Exactly same error codes as in your situation and mirroring wouldn't resume. Log drive was completely full on the mirror.

Luckily I was able to free up some space by deleting a couple of stray old logs on the Log drive. After that, was able to resume the mirroring.

Then went into the maintenance plan backups and triggered the database and transaction log backups manually. Even after the back-up the transaction log files were still huge. So I had to repeatedly tell SQL Studio to shrink the log files (free up any unused space), and after a few attempts over a couple of hours, it cut them down to a more manageable size.

Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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