Shrinking the transaction log of a mirrored SQL Server 2005 database

Question

I've been looking all over the internet and I can't find an acceptable solution to my problem, I'm wondering if there even is a solution without a compromise...

I'm not a DBA, but I'm a one man team working on a huge web site with no extra funding for extra bodies, so I'm doing the best I can.

Our backup plan sucks, and I'm having a really hard time improving it. Currently, there are two servers running SQL Server 2005. I have a mirrored database (no witness) that seems to be working well. I do a full backup at noon and at midnight. These get backed up to tape by our service provider nightly, and I burn the backup files to dvd weekly to keep old records on hand. Eventually I'd like to switch to log shipping, since mirroring seems kinda pointless without a witness server.

The issue is that the transaction log is growing non-stop. From the research I've done, it seems that I can't truncate a log file of a mirrored database. So how do I stop the file from growing!?

Based on this web page, I tried this:

USE dbname
GO
CHECKPOINT
GO
BACKUP LOG dbname TO DISK='NULL' WITH NOFORMAT, INIT, NAME = N'dbnameLog Backup', SKIP, NOREWIND, NOUNLOAD
GO
DBCC SHRINKFILE('dbname_Log', 2048)
GO

But that didn't work. Everything else I've found says I need to disable the mirror before running the backup log command in order for it to work.

My Question (TL;DR)

How can I shrink my transaction log file without disabling the mirror?

Answer 1

Well, technically it is possible to shrink a mirrored LOG. What is doing trouble is to backup log with truncate_only. Mirroring doesn't accept it. So one way is to perform a backup log to disk:

use [DATABASE_NAME]
checkpoint
BACKUP LOG [DATABASE_NAME] TO DISK =  'C:\LOG_BACKUPS\DATABASE_NAME'
dbcc shrinkfile(DATABASE_NAME_Log,1)

This is part of our current maintenance plan and it has been working withot problems for about 2 years.