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