Post reply

HUGE transaction Logs

  • February 20, 2007 at 4:56 pm

    #61104

    Hi All

    I have a question regarding Transaction Logs on the SQL database. When I run optimizations on my Solomon database, I get a huge transaction log. Actually bigger than the backup file. I have other databases that are about the same size, but I don’t get a huge transaction log from them. Any Ideas?

    Thanks

    Bill

  • February 20, 2007 at 5:57 pm

    #690783

    I think ur database recovery is set to full which can cause the xcation log to grow if u don't have xcation log backup.

    Either change the recovery mode or run fullbackup or xcation log backup immediately after your optimization and if you want it to shrink run dbcc shrinkdatabase.

    Thanks

    Sreejith

  • February 21, 2007 at 10:45 am

    #690981

    Your optimization may have reindex option which will consume a lot of T-log space. 

    see Table 5 here: http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

    Log space usage

    High in full recovery mode (logs entire contents of the index), low in bulk logged or simple recovery mode (only logs allocation of space)

  • February 21, 2007 at 12:07 pm

    #690999

    This article also has a good explanation of what's going on.

    I noticed the same thing when we added optimization to our weekend routine. We ran a nightly full recovery backup and a noontime transaction backup, and hadn't differentiated weekday from weekend. So, full backup at 2am Sunday, followed by a very long optimization run (reindex), equaled a tranlog backup rivaling the full backup in size.

    To fix this, I simply turned off the Sunday noon tranlog backup. We didn't have any activity on Sundays, so it was pointless anyway. Another thing that might've worked was to do the optimizations first, then follow it by a full backup, which resets the tranlog.

    ..jeremy.

  • February 21, 2007 at 12:30 pm

    #691007

    Thanks all

    That makes complete sense now that I think about it. This fourm is great!

    Bill

  • February 22, 2007 at 8:52 am

    #691186

    If you're going to run a full backup after the reindexing, you might speed it up (and prevent log file growth) by switching to simple recovery mode during the optimization.

    Regular full backup

    Switch database to simple recovery mode

    Use sp_delete_maintenance_plan_db to remove the database from the tran log backup plan, if it might run during the optimization

    Run optimizations

    Switch database to full recovery mode

    Full backup

    Use sp_add_maintenance_plan_db to put the database back in the tran log backup plan

Viewing 6 posts - 1 through 5 (of 5 total)

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