June 27, 2008 at 11:38 am
I have a database which uses the simple recovery model. The MDF is 270MB but the full backup file is nearly 1gb. I thought that this might be due to the size of the log file, but the database's log file is a mere 2mb (I had applied a shrinkfile to it awhile ago, with a small target size). It hasn't grown. Can someone explain what is going on here? Why is the BAK file so much larger than MDF.
June 27, 2008 at 11:53 am
It sounds like your backup is being appended to. Have a look at the "INIT" option. That controls whether the an overwrite or append operation happens.
To see how many backups are stored in that .BAK file run a RESTORE HEADERONLY.
June 27, 2008 at 12:41 pm
Thanks, Todd, you were right. Forgot to add the WITH INIT clause to my BACKUP job. One other question: How do I go estimating what the target size of my log file should be? ...Keeping in mind that my backup approach uses simple recovery model and weekly full backup and nightly differentials?
June 27, 2008 at 1:34 pm
From a recovery perspective it doesn't matter. You can only use the Full and Differential backups with the Simple recovery model.
As for what size it should be initially that would be dependant on your environment and what you're doing; such as index rebuilds and loading data. Using a Perfmon log to monitor the appropriate log counters for your database over a week or month should give an indication of what's normal.
June 27, 2008 at 7:06 pm
Once again, Todd, Thanks so much for your great help!
June 27, 2008 at 9:46 pm
The log size also depends on how often you run log backups.
If I can generate 100MB of logs in a day from my database, if I backup every hour, I might only need a 10MB log. If I backup once a day, I'd need 100MB+
The log size is the peak amount of transactions generated between log backups + some pad.
June 28, 2008 at 7:52 am
>Forgot to add the WITH INIT clause to my BACKUP job.
Just consider the risk with overwriting your existing backup before the new backup has succeeded.
Ola Hallengren
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply