Shrinking the transaction log of a mirrored SQL Server 2005 database

Posted by Peter Di Cecco on Stack Overflow See other posts from Stack Overflow or by Peter Di Cecco
Published on 2009-06-23T15:07:33Z Indexed on 2010/05/22 13:00 UTC
Read the original article Hit count: 222

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?

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about database