How do I shrink the physical Transaction Log file when it's the principal in a mirror?

Question

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

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.

Answer 2

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.