Post reply

Log Backup Increases Crazily in Size

  • December 28, 2006 at 6:30 pm

    #171422

    We just upgraded from SQL 2000 to SQL 2005 3 weeks ago.  We backup the databases nightly and the trans logs every 30 minutes during biz hours.  With SQL 2000, the size of log backup files were managable ranging between tens MB to hundreds MB for about 30 databases.  We are doing same thing with SQL 2005; trans logs are backed up every 30 minutes during biz hours.  However the log backup files are now four, five times larger or more.  For example one database when we had SQL 2000 had its log backup file less than 1 GB in size after the last log backup of the day.  Now with SQL 2005 this same database has the log backup file reaches up to 10GB.  Same acitivies everyday for the databases.  Is there any thing you can thing of as the cause?  Thanks.

  • December 28, 2006 at 9:29 pm

    #680446

    Is snapshot isolation level or mirroring is enabled?

    MohammedU
    Microsoft SQL Server MVP

  • December 28, 2006 at 11:14 pm

    #680452

    nope.  All are default.

  • December 29, 2006 at 7:48 am

    #680496

    are you doing things in parallel now that you didn't do before? like large updates and reindexing at the same time?

  • December 29, 2006 at 9:34 am

    #680536

    There is only one thing different: the way we update data.  We often need to update lookup tables.  With SQL 2000 the data import-export tool let us drop and recreate the tables.  With SQL 2005 we empty the tables first and import the new data.  I am not sure how the developers do but usually they drop the FKs, delete the tables, import new data, and recreate the FKs.  I suspect the transaction log grows when the tables are deleted (data deleted) but I can't explain why it didn't grow in SQL 2000.

  • December 29, 2006 at 10:49 am

    #680553

    have you tried truncating? I think delete causes a lot more data to be logged in the transaction log

  • December 29, 2006 at 11:54 am

    #680573

    Like SQL Noob recommended, if they went all the trouble to delete/disable the foreign keys they may as well should use TRUNCATE TABLE instead of delete. DELETE does generates *a lot* of log activity.

    Cheers,


    * Noel

  • December 29, 2006 at 3:08 pm

    #680605

    Truncate is not logged.  I was just afraid it would break the LSN in the transaction log causing log restore failed.  Even I did a test and was able to restore to the last log backup after a TRUNCATE but I am not at ease.

    Thanks everyone.

  • December 29, 2006 at 4:32 pm

    #680611

    Truncate is minimally logged operation and it will not break LSN...

     

    MohammedU
    Microsoft SQL Server MVP

  • December 30, 2006 at 8:58 pm

    #680632

    What is the build of SQL Server?

    Can you please check the log growth set for the database. There is a known issue with SQL Server 2005 where the growth is getting set to very high number (sometimes 12400%) so whenever there is autogrow happens it will increase log file size as you are expecting.

    There are hotfixes for this problem.

    FOR SP1

    FIX: The value of the automatic growth increment of a database file may be very large in SQL Server 2005 with Service Pack 1

    <http://support.microsoft.com/kb/919611/en-us>

    FOR RTM

    FIX: The value of the automatic growth increment of a database file may be very large in SQL Server 2005 <http://support.microsoft.com/kb/917887/en-us>

    Contact Microsoft and get a grace case opened to get hotfix.

  • January 2, 2007 at 10:21 am

    #680788

    SQL build: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)   Apr 14 2006 01:12:25   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

    It is an HP dual dual-core AMD Opteron 270 Processor with 2 GB of RAM.  The log growth is set auto but the problem I have with is the backup of the log, not the log.  The size of the log is normal.  At night I do a final log backup, truncate the log, and do a full backup.  The percentage of log usage is 1%, 2% of 500 MB in the morning and grows up to 80%, 90% at the end of the day.  However the backup of the log is up to 8 GB , 10 GB at the end of the day after the final log backup.  The hot fix doesn't seem to fit my case.

  • January 2, 2007 at 11:19 am

    #680797

    How often you run the tlog backup?

    If your recovery model is full/bulk_logged, log will not be truncated unless you run the tlog backup.

    If the db is configured for transactional replication, log will not be truncated if there pending transactions to replicate even after running the tlog backup.

     

     

    MohammedU
    Microsoft SQL Server MVP

  • January 2, 2007 at 11:49 am

    #680802

    the log backup runs every 30 minutes from 6am to 10pm.  The recovery mode is full.  The database is setup with default: no replication, no mirroring.

  • January 2, 2007 at 12:11 pm

    #680808

    "However the backup of the log is up to 8 GB , 10 GB at the end of the day after the final log backup."

    Is your .ldf files grows to 8-10 GB or backp file?

    Are you appending the log backups to single file?

    MohammedU
    Microsoft SQL Server MVP

  • January 2, 2007 at 3:23 pm

    #680856

    The log backup files grows, not the ldf files.

    Yes, I appending the log backups into a single file. 

    It seems that the log backup job keeps backing up starting from the last checkpoint.  Is there a way to checkpoint the log?  Here is what I have from RESTORE HEADER ONLY.

    BackupSizeFirstLSNLastLSNCheckpointLSNDatabaseBackupLSNBackupStartDate
    274432673130000000044000376731300000000750000167313000000004400037673130000000044000372007-01-02 05:45:08.000
    274432673130000000044000376731300000000770000167313000000004400037673130000000044000372007-01-02 06:15:07.000
    274432673130000000044000376731300000000770000167313000000004400037673130000000044000372007-01-02 06:45:07.000
    274432673130000000044000376731300000000790000167313000000004400037673130000000044000372007-01-02 07:15:08.000
    274432673130000000044000376731300000000830000167313000000004400037673130000000044000372007-01-02 07:45:08.000
    274432673130000000044000376731300000000910000167313000000004400037673130000000044000372007-01-02 08:15:09.000
    28272640673130000000044000376732700000020240000167327000000188600001673130000000044000372007-01-02 08:45:09.000
    56660992673130000000044000376733800000012010000167338000000012900001673130000000044000372007-01-02 09:15:13.000
    84325376673130000000044000376734600000045240000167341000000313100338673130000000044000372007-01-02 09:45:10.000
    111988736673130000000044000376735300000053360000167353000000475500001673130000000044000372007-01-02 10:15:10.000
    139520000673130000000044000376735900000060470000167359000000556500001673130000000044000372007-01-02 10:45:11.000
    166984704673130000000044000376736400000090740000167364000000791600001673130000000044000372007-01-02 11:15:13.000
    167312384673130000000044000376736400000096470000167364000000791600001673130000000044000372007-01-02 11:45:12.000
    168034304673130000000044000376736500000003050000167364000000791600001673130000000044000372007-01-02 12:15:14.000
    194973696673130000000044000376736900000069200000167366000000661600135673130000000044000372007-01-02 12:45:29.000
    222765056673130000000044000376737300000105000000167373000000873700001673130000000044000372007-01-02 13:15:20.000
    223289344673130000000044000376737300000115140000167373000000873700001673130000000044000372007-01-02 13:45:15.000

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply