BAK file many times larger than MDF file

Question

BAK file many times larger than MDF file

john.cunningham

SSC Veteran

Points: 262
More actions
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

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

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

Khwaja Arshaduddin SSC Eights! Points: 843 More actions · Answer 1

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.

Lowell SSC Guru Points: 323518 More actions · Answer 2

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!

john.cunningham SSC Veteran Points: 262 More actions · Answer 3

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