tran log backup confused 2.5gb log file but 70gb log backup size

  • Ok I have a question I have a 116gb DB the full backup ends up around 70.5GB in size I then perform a transaction log backup which the log file is 2.5GB in size but the log backup ends up being 71.5GB in size.

    This is sql 2005 latest sp.

    the db is in bulk logged recovery model.

  • lawson2305 (6/9/2012)


    Ok I have a question I have a 116gb DB the full backup ends up around 70.5GB in size I then perform a transaction log backup which the log file is 2.5GB in size but the log backup ends up being 71.5GB in size.

    This is sql 2005 latest sp.

    the db is in bulk logged recovery model.

    Since the database is using the bulk logged recovery model, it looks like you may have had minimally logged operations. When you back up the t-log, the actual extends updated are backed up to the t-log backup. This keeps the t-log itself small but the backup can be much larger.

  • Bulk-logged recovery is why.

    http://www.sqlservercentral.com/articles/Recovery+models/89664/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Here is another article for your reference that helps explains why bulk logged is not reducing the size of your tlog backups:

    Using Bulk Logged recovery model for Bulk Operations will reduce the size of Transaction log backups – Myths and Truths

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

  • Ok I have changed it to full recovery and the tlog is still running at 71GB. Does this make sense? Do I need to do something to get the tlog backup small like the actual tlog?

    I have done a full db and tlog backup after changing to full recovery model.

  • What is the result of the following query.

    select name, log_reuse_wait_desc from sys.databases;

  • Silly question--you're not backing up both the full and transaction log backup to the same file, are you? A SQL backup file can contain multiple backups.

  • No I am not backing up to the same file.

    full to a .bak

    log to a .trn

  • masterNOTHING

    tempdbNOTHING

    modelLOG_BACKUP

    msdbNOTHING

    ReportServerNOTHING

    ReportServerTempDBNOTHING

    ActivplantDBLOG_BACKUP

  • If your database is heavily indexed, and you are rebuilding indexes frequently, this could account for the large log backups. This would especially be true if you have a small fill factor on some indexes, and its causing lots of page splits.

    If that is the case, read:

    David Lathrop
    DBA
    WA Dept of Health

  • my second backup finally fixed this.

    Log backups now down to 1.6GB for the week.

  • 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.

  • 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.

    Have you read the link supplied to you by Gail Shaw?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • 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.

    And what does the following show for the database in question?

    select name, log_reuse_wait_desc from sys.databases;

  • 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

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

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