SQL Server Performance

backup file size

Discussion in 'General DBA Questions' started by marcgale@yahoo.com, Dec 16, 2002.

  1. marcgale@yahoo.com New Member

    I have a db approx 26G in size. Transaction log is ~4G. I do a full backup and the .bak file is 51G! Why? Thanks in advance for any thoughts.
    Marc
  2. bradmcgehee New Member

    First, check to see if you have not accidently appended the backup to another backup. This is most likely cause.

    If that is not the case, consider doing the following:

    --DBCC CHECKDB (if this runs without errors, then do the following)
    --DBCC UPDATEUSAGE
    --Rebuild all of the indexes in your database
    --Truncate the transaction log
    --Perform another backup and see what happens

    The above may not solve the problem, but it won't hurt it either.

    Also, check to see that you have the latest SQL SErver Service Pack just in case in issue is related to a fixed bug.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. royv New Member

    i agree with brad on the appending possibility. i did this one time and almost had a heart-attack trying to figure out what was going on! also if you are going through enterprise manager, make sure to remove any existing backup devices and recreate a new backup device. i do this to personally make me feel better that i am getting the correct backup.


    "How do you expect to beat me when I am forever?"
  4. satya Moderator

    You might have use NOINIT clause under your BACKUP statement which will append the backup as specified by above. Following Brad's suggestions will help you to keep DB healthy and well performed. Worth performing regular DB maintenance checks.

    HTH

    Satya SKJ

Share This Page