SQL Server Performance Forum – Threads Archive

Log shipping and Optimization Job

Our Production Database (70GB) log ships transactional logs every 15 mins to the stand by server. Log shipping works fine all week along, except when we run the ‘optimization job’ – which ‘reorganizes data and index pages’. During the run of optimization job, the transactional log gets very huge, which makes is it very timely to backup, copy and restore. Also during the optimization job, there are deadlocks being created on the database causing an outage. Is there a way to get around this? Maybe stop log shipping for the 3 hour period of optimization job and restart upon completion? Thank You.
tcgandhi

We have the same problem. And what we are doing is
1) Change the database recovery model to ‘Bulk logged’ before starting the optimization job, to reduce the t-log file size. 2) Truncate the log and take a full backup after the optimization and restore this full backup to the standby database.

True and in this case you need to test and pause the log shipping jobs until the above process is completed. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I’ve configured my log backup job to trigger the log shipping jobs. So the log shipping job runs only whene there is a new log backup. Hence, no need to pause anything.

]]>