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

If the mirror server instance falls behind the principal server instance, the amount of active log space will grow. In this case, you may need to stop database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database and restart mirroring, not the answer you were hoping for, I know =(

To shrink our files you could try the following script:

exec sp_dboption DBName, 'trunc. log on chkpt.', true checkpoint DBCC SHRINKFILE (DBNameFileName, 500); exec sp_dboption DBName, 'trunc. log on chkpt.', false

Hope this helps.

Answer 2

I thought I should actually answer this seeing as it was left forgotten about.

Turns out, you can't shrink a t-log if the database is mirrored unless you deactivate the mirror. If I'm wrong, please correct me, but I've found no solution that works!

Log shipping is the way to go if you only have two servers. Mirroring is almost pointless without a witness server, because the only way to failover is from the principal... kinda defeats the purpose of having a mirror if you can't failover when the principal crashes.

If anyone cares to share more info or suggestions on this matter, I will be welcome to hear them.

Answer 3

the only way: 1) Stop mirroring 2) shrink files on principal 3) backup complete of principal, + transaction jrnl 4) stop mirror server, delete mdf and ldf of mirrorDatabase 5) start mirrorser and delete mirrorDatabase 6) restore with no recovery backups of 3) on mirroServer 7) reinstall mirroring Ouf !

Answer 4

It's true that you can't shrink the database log once it's got too big - at that point I think your only option is to break the mirror, shrink and re-create. Further, notwithstanding the issues of whether you ought to be using mirroring with just two servers, what I can say is that if you do then regularly backup the transaction log. The space will be released from it thereby allowing MSSQL to re-use the dead space within the log file. This doesn't shrink anything but it does meet the requirement of stopping it growing.

Then all you need to do is regularly delete the file backups. For example you could do this:

USE your_database
GO
BACKUP LOG your_database TO DISK = 'x:\your_backup_filepath\your_database.tlog'
GO

Do it out of hours though if you can.

Answer 5

I don't know why this works, only that it does indeed work. I run this as a block in a query window. Use at your own discretion. Sure would be nice if a microsoftie would comment.

use my_database
dbcc shrinkfile ( my_database_log, 1000 )
use my_database
dbcc shrinkfile ( my_database_log, 1000 )
alter database my_database
  modify file ( 
    name = my_database_log, 
    size = 1000MB
  )