SQL Server Performance

Log shipping - shrinking log and data file

Discussion in 'SQL Server Log Shipping' started by magictech, Dec 21, 2004.

  1. magictech New Member

    I'm having problem managing log and data file sizes while log shipping is configured and running properly. I have a re-indexing job that runs weekly and after this jobs runs, the log and data file sizes of the database that's being log-shipped increases by an additional 40Gig each. I would like to be able to shrink the data and log files of the primary database to their normal sizes without breaking log shipping. I realize the process of shrinking data and log files will automatically truncate the transaction log, which will break log shipping. Is there a way to shrink these files without breaking log shipping? Any information would be greatly appreciated.

    Regards
  2. satya Moderator

    In any case there is no chance to fix the log shipping as the transactions after previous log backup will be changed and mismatch in LSN sequence.

    The other option is to enable SIMPLE recovery model until reindex job finishes and apply full backup to secondary server for further log shipping.

    Also consider reindexing the indexes on the table which has frequent updates/deletes of data, otherwise its waste of time and resource to reindex all the tables which might contribute the transaction log to bulge.

    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.

Share This Page