Post reply

Size of transaction log keeps growing even after backup

  • April 22, 2008 at 12:24 pm

    #123455

    Hi all,

    I am having a issue with sql server 2000 and at any given time even after the backup I see the transaction log grow to many GB's in size.

    I have truncated / shrunk the size last week and now i see the log at 8 GB again and this is causing disk space issues.

    The backups are done on a nightly basis along with the transaction log but what is going on to make the TL keep growing to this size?

    I am not an expert with sql server , only have working knowledge.

    Should I create a new maintenance plan ?

    Thanks

  • April 25, 2008 at 3:46 am

    #807032

    Hi,

    Can you please let me know how frequently transactional log backups are taken. Try to schedule it to every 30 minutes or hourly basis if possible. There is no meaning in truncating the database log. The log files grow according the transactions happenning on the database. So instead of truncating try to take the transactional log backups. If you have space issue on the drive, Add another transactional log file pointing to other drive where you have free space and restrict this file growth.

  • April 25, 2008 at 6:41 am

    #807115

    Thanks for the reply...

    The TL log seems to grow to several GB and never reduces even after a backup several times a day.

    Is there something wrong with th DB ?

  • April 25, 2008 at 9:27 am

    #807248

    To emphasize what Vee said, if you do transaction log backups frequently enough, log file growth should be manageable. Keep in mind that a log backup won't shrink the physical log file. It will only truncate the inactive portion of the log after backing up the transactions, thus reducing the need for the log file to grow.

    Greg

  • April 25, 2008 at 11:21 am

    #807329

    Ok I get it , so just back up the log file hourly and it won't grow to a point where it just takes space unecessarily. So I should do shrink log once again ?

    I'm just starting with SQL server ... i'm an Oracle DBA.

    Thanks again

  • April 25, 2008 at 11:39 am

    #807339

    How frequently you backup the t-logs depends on how busy transaction-wise the database is. Hourly works for most of our OLTP databases.

    You can shrink the file once you have log backups scheduled, but you shouldn't do it at a time when the database is heavily used. Don't shrink the file to it's initial size, but try to leave enough space allocated so it won't have to grow a lot between backups.

    Greg

  • April 25, 2008 at 11:41 am

    #807341

    Thanks ! will let you know the results.

    JR

  • April 29, 2008 at 9:34 am

    #808489

    I verified the TLOG backups and they are done every hour. The log file is relatively small ( 800 mB ) after the backup, but then at any given time even after the hourly TLOG backups the files is always between 3 and 10 GB !

    Can there be a looping process somewhere?

  • May 1, 2008 at 8:16 am

    #809568

    Hi,

    It looks like, its a heavily used database and a lot of transactions to be saved. You may need to add the additional log file pointing to free disk. Taking log backup and truncating will not work in this case. The other way around i believe is one hour is too long for heavily happenning transactions. Try to reduce it to 15 minutes. We have huge database and set up to 15 log backups.

    The other way around is, if the database is not that much critical you can change the recovery model to simple and stop the transactional log backup job. Then the log size will be automatically managed.

  • May 1, 2008 at 8:36 am

    #809600

    Hi Vee... thanks for those tips , I'll try these different things and see if the file is better managed.

Viewing 10 posts - 1 through 9 (of 9 total)

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