trans log backup weirdness...

  • I have a trans log backup job that is scheduled once every 30 minutes on a low-medium activity stand-alone SQL 2000 sp3a server. 

    The backup job failed with a not enough space error message over the weekend. 

    This morning I made sure there was enough space (always was enough, job was wrong to say otherwise) and manually kicked off trans log backup.

    Before backup trans log shows 3.3 gigs used out or 4.5 gigs total.

    Backup finishes in 12 minutes and clears trans log.

    Backed-up trans log on disk shows a total size of 7.83 gigs!

    How can this be?  How can a backup file be bigger than what was being backed up?  Has anyone had this happen to them?

    Thanks in advance for any input.

    T. Johnson

  • How are you backing this up?  If you're appending, the backup file will continue to grow. 


    And then again, I might be wrong ...
    David Webb

  • Transaction log backups write a new file, unique, file for each time the backup job is run.  This is to make the restore to a distinct point in time, that every application mananger thinks that he has to flippin' have but never-ever has he used it, possible.

    Thanks!  T. Johnson

  • Where do you get the size of your log file from?

    There are two actual sizes of the log file. First, the size of the log's data and second, the size of the log's data and free space. The free space is still part of the log file and is backed up.

    -SQLBill

  • The total size of the transaction log, free space and used, is 3.3 gigabytes.  The total size of the transaction log backup onto disk was 7.83 gigabytes.

    Thanks; T. Johnson

  • I had something similar happen when I changed a database to Bulk-logged recovery mode than ran the optimization task to reindex the database and then I put the database back into full mode. At the next transaction log backup it created a huge file, more that the current size of the transaction log file.

  • Here is the thread that details the similar issue I was having:

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=192268#bm192409

  • Thanks Michelle!  I too have a weekly job that rebuilds all indexes in that database after switching the database to BULK_LOGGED first, then back to FULL after the job was finished.  That would explain everything. The strange thing is that I have this same job configuration setup on several databases, but this is the only one that has a problem.

    Just the same, I'm going to implement the steps you listed in your post.  They seem sound to me.

    Thanks again for the help!  T. Johnson

Viewing 8 posts - 1 through 7 (of 7 total)

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