SQL Server Performance

SQL Server log abckups

Discussion in 'SQL Server Log Shipping' started by hshapiro, Dec 5, 2005.

  1. hshapiro New Member

    I have 2 servers et up in a log ship scenario. Every hour, I backup the tran log and restore it on the other server. The log backup file normally ranges from 100 K to 200 Meg. On Sunday AM, the log backup file goes to 12 Gig. The database is 19 Gig normally and the log file is about 4 Gig normally. The only other thing I see on Sunday mornings is the Optimization and Integrity check job. Any ideas on why somethign like this is happening?
  2. Luis Martin Moderator

    Optimization, like reindex, will grow log because you have full recovery model and database change (a lot) after optimization. Tha's wy your log grow.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. satya Moderator

    True and you must enable the transaction log backup schedule more frequently.
    http://www.sql-server-performance.com/absolutenm/templates/?a=260&z=1 for reference.

    If possible you can switch the recovery models from FULL to SIMPLE while reindexing and optimization job runs and then perform full backup then re-enable transaction log backup 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.
  4. hshapiro New Member

    Would I necessarily have to do a full backup after the Optimization job is done?
  5. satya Moderator

    In case if you change the recovery model from FULL to SIMPLE and in order to continue the log backups you must perform the full backup. Otherwise it is not needed and in this case it is better to review the log size and set it up.

    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.

Share This Page