Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Truncating the Log and Database Mirroring

I saw someone post recently that they wanted to run this code on their principal server in database mirroring to clean out a large transaction log and shrink it.

alter database <mydb> set recovery simple
go
checkpoint
go
alter database <mydb> set recovery full
go
backup database pubs to disk = 'c:\mydb.bak' with init
go
dbcc shrinkfile (N'mydb_log' , 1)
go


I responded, and a few others as well, that this will break your mirroring session. However I wanted to do some research, and I started googling around to see what I could find.



While I haven’t found an explicit reference, I did note that Paul Randal has a note that you can switch to Bulk Logged mode without breaking the log chain, but I knew that.



I don’t have a definitive reference, though I’m hoping one appears soon. The documentation for Books Online does say that full recovery mode is required.



If you have a log file that’s too large, you can run shrinkfile on it, even if it is mirrored. That will work, but don’t set the database to Simple mode. Manage the log backups, and make sure you have them running regularly. And if you need help shrinking the log, try this script.

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.