Home Forums SQL Server 2005 Backups tran log backup confused 2.5gb log file but 70gb log backup size RE: tran log backup confused 2.5gb log file but 70gb log backup size

  • lawson2305 (7/16/2012)


    ok I just don't get it. They are back up to 66GB again.

    I run a tran backup and shrink every day. I append the backup daily except for Saturday I do a overwrite.

    The tran backup is now 66 GB and the ldf file is 66GB with 99% available free space.

    Something is causing your log to bloat. One usual suspect is an index maintenance job. Stop shrinking your tran log every day first of all. As you can see it will simply grow again. The next course of action is to figure out what is causing the log to bloat. Look for index maintenance jobs, large batch processing jobs, rogue apps not closing their transactions, anything that would create a lot of modifications to data or indexes. That can help in troubleshooting. Is your database a publisher in a transactional replication scheme? Also try to narrow down the time frame.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato