Post reply

BAK file many times larger than MDF file

  • February 17, 2009 at 4:01 am

    #200091

    Hi all

    This morning when checking my SQL2005 Ent cluster I noticed that the available space on the data drive had dropped. On further investigation I found that for one of my databases the BAK backup file was many times larger than the MDF file. Generally I find that the BAK and MDF files are very approximately the same size but for this one database the MDF is 5.5 Mb while the BAK files are 380 Mb.

    The ldf file is 400 mb but the daily trn log backups are only 9 Mb.

    I backup a number of databases in the same maintenance plan so the backup parameters are the same for all. Can anyone explain why this one database is producing such large BAK files?

    TIA

    JohnC

  • February 17, 2009 at 4:38 am

    #945075

    When you take Log backup the unwritten transactions are written to the disk and your log file will reduce after the backup completes so you may experience the large log backup.Full backup is combination of log and datafiles so it is aggrigated and you may feel the backupsize is large.

  • February 17, 2009 at 4:50 am

    #945084

    Also could it be that the log file is being appended to instead of overwritten WITH INIT, so the big backup actually contains 30 or so regular 5 Meg backups inside it?

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 17, 2009 at 5:24 am

    #945095

    Hmmmm - the more I look at this the less I know!

    Thanks guys - but still not clear what is what here.

    The maintenance plan operates on about 20 databases. First it runs a full backup to disk .BAK files. Then it runs a transaction log backup to a different disk .TRN files (is that the right order?)

    For all the other databases the actual log file and BAK files do not grow much but for this one database the logfile never seems to be truncated.

    Off to do some more RTFMing in the Bol

    j

Viewing 4 posts - 1 through 3 (of 3 total)

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