Log shipping and shrinking transaction logs

I just solved a problem that had me worried for a bit. I'm log shipping from three primary servers to a single secondary server, and the transaction log disk on the secondary server was getting very full. I established that several primary databases had unused space that resulted from big, one-off updates so I could shrink their logs. But would this action be log shipped and applied to the secondary database too? I thought probably not. And, more importantly, would it break log shipping? My secondary databases are in a Standby / Read Only state so I didn't think I could shrink their logs. I RTFMd, Googled, and asked on a Q&A site (not the evil one) but was none the wiser.

So I was facing a monumental round of shrink, full backup, full secondary restore and re-start log shipping (which would leave us without a disaster recovery facility for the duration). Then I thought it might be worthwhile to take a non-essential database and just make absolutely sure a log shrink on the primary wouldn't ship over and occur on the secondary as well. So I did a DBCC SHRINKFILE and kept an eye on the secondary. Bingo! Log shipping didn't blink and the log on the secondary shrank too.

I just love it when something turns out even better than I dared to hope. (And I guess this highlights something I need to learn about what activities are logged.)

Comments

# thomas said:

David, what version of SQL Server was this? I have seen the opposite in 2005 log shipping, where I've shrunk a data file and the shrink didn't get applied to the standby server. It required a full backup/restore to get the log shipped copy to shrink.

04 March 2010 14:28
# DavidWimbush said:

Hi Thomas,

This is SQL 2005 32 bit with Service Pack 3 (version 9.0.4035). The primary server is Workgroup Edition and the secondary server is Standard Edition (so I have the option of using the standby for reporting at a pinch).

05 March 2010 11:48