SQL Server 2005/2008: Shrinking the log file of secondary database in Log Shipping setup
Platform : SQL Server 2005 and 2008
I had set up log shipping between two servers and most days the size of the log file of the primary database remains within acceptable limits. But today there was some bulk load and that resulted in a huge log file. Consequently the log file size of the secondary database (Database to which the logs were being shipped and restored) also got big. So big that the disk drive where the log file was located was almost full.
So I had to figure out a way to shrink the log file of the secondary database but unfortunately it was in READ ONLY mode so there was no way I can shrink that database. Until today I had not reached this situation where I have to shrink the log file of secondary database. Just to try I ran the DBCC SHRINKFILE command against the primary database and that did the trick. This shrank the log file of the secondary server too. But before this happened, I had to run the LSBackp, LSCopy and the LSRestore job to make sure that the transaction was copied over to the secondary database.
Conclusion: In a Log Shipping set up in SQL Server 2005/2008, DBCC SHRINKFILE on primary database does get propagated to the secondary database.
Platform : SQL Server 2005 and 2008
I had set up log shipping between two servers and most days the size of the log file of the primary database remains within acceptable limits. But today there was some bulk load and that resulted in a huge log file. Consequently the log file size of the secondary database (Database to which the logs were being shipped and restored) also got big. So big that the disk drive where the log file was located was almost full.
So I had to figure out a way to shrink the log file of the secondary database but unfortunately it was in READ ONLY mode so there was no way I can shrink that database. Until today I had not reached this situation where I have to shrink the log file of secondary database. Just to try I ran the DBCC SHRINKFILE command against the primary database and that did the trick. This shrank the log file of the secondary server too. But before this happened, I had to run the LSBackp, LSCopy and the LSRestore job to make sure that the transaction was copied over to the secondary database.
Conclusion: In a Log Shipping set up in SQL Server 2005/2008, DBCC SHRINKFILE on primary database does get propagated to the secondary database.
Expert DBAs suggest that try to avoid use of truncate or shrink LDF file during log shipping enabled on the database because It will break the LSN (log sequence number) of transaction log backup. If you have done this then your log shipping will break. Once you shrink log file, you have to take full backup and then reset the log shipping.
ReplyDeleteRegards,
Mark Willium
SQL Server Disaster Recovery Specialist
www(dot)stellarsqldatabaserecovery(dot)com
I am sure if you try the following command it will break the LSN and you will have to reinitialize the Log shipping setup.
ReplyDeleteBACKUP LOG WITH TRUNCATE_ONLY
But this command has been deprecated and should never be used. But if you use the DBCC Shrinkfile command then the LSN does not break and LS works fine. I have tested this and hence the blogpost.
yes your resolution is perfect. thanks you. very much.
ReplyDeleteYou dont have to run BACKUP LOG WITH TRUNCATE_ONLY, just run the Logshipping backup Job a few times to inactivate the VLFs
ReplyDeleteWe do this all the time on our Logshipping DBs... works like a charm
Great one Kaykay
Thanks a bunch for your post.
ReplyDeleteWorking fine for me too.