SQL Server Performance

Log shipping and Optimization Job

Discussion in 'SQL Server Log Shipping' started by tcgandhi, Oct 18, 2004.

  1. tcgandhi New Member

    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
  2. ykchakri New Member

    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.
  3. satya Moderator

    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.
  4. ykchakri New Member

    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.

Share This Page