SQL Server Performance Forum – Threads Archive

SQL Log Shipping recovery models

Hi, We have several large databases (500GB+) that we log ship to provide redundancy and other uses. We have started doing extensive re-indexing jobs on these databases over night at the weekends, which has caused some rather large transaction logs (anywhere up to 15GB). The recovery model is set to Full at the moment, and usually this is fine during day to day activities. My question is: Can we set the recovery model to Bulk before the reindexing and reset back to full afterwards again to get these files reduced in size. Would this then speed up the restore process on the standby server – which currently takes anywhere up to 10-15 hours to apply these transaction logs. This obviously causes a bottleneck which means that the standby server gets out of date quite quickly, taking a day or two to catch up again. Are we going to miss any transactions by setting it to bulk instead of full? What other implications do we need to consider? Any help would be gratefully appreciated! Cheers.

Before doing bulk copy operations, it is recommended that you set the recovery model to bulk-logged if you usually use full recovery. This will prevent the bulk copy operations from using excessive log space and possibly filling the log. However, even with bulk-logged recovery, some transaction log space will be used. You may want to create transaction log backups during the bulk copy operation to free up transaction log space. When bulk copying a large number of rows into a table with indexes, it can be faster to drop all the indexes, perform the bulk copy, and re-create the indexes. But in any case it is better to perform frequent transaction logs for consistency. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I haven’t done this myself, but this would seem to indicate that you can switch. http://www.microsoft.com/technet/community/chats/trans/sql/sql0528.mspx
Q: Can you have the primary database’s recovery model set to "Bulk-Logged"? Can you switch between "Bulk-Logged" and "Full" without interupting the shipping process? A: You can have the primary db in Bulk-logged or Full recovery models. You may also switch the models.

We had few problem when dealing with this option, due to importance of the database server we have to put this back and use proper switching without interrupting the log shipping. I’m sure it is achievable but you have to tryout the options like trial and error basis. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks guys for the help, I guess what I am after is knowing if anyone has experienced reindexing with bulk mode transaction log backups instead of full recovery mode. Will the transaction log backup files be a lot smaller with bulk mode after performing a reindex, or will they be the same size as the full recovery model backups? cheers

I believe there will not be much change in between them, as BOL states When BULK_LOGGED is specified, logging for all SELECT INTO, CREATE INDEX, and bulk loading data operations is minimal and therefore requires less log space. In exchange for better performance and less log space usage, the risk of exposure to loss is greater than with full recovery. But you can overcome with frequent transaction log backup by reducing the virtual size of log to carry upon the optimization jobs. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for all your help, I am putting together a test to examine the differences that bulk logging has on the transaction log backups and log shipping. I’ll post the results in here. Cheers.

]]>