Why is SQL Server Backup File Occasional Larger?

Every week night (not Saturday and Sunday) a stored procedure is scheduled to run to backup our SQL Server database "Altek".  Altek.mdf is about 5 GB.  The stored procedure uses the BACKUP DATABASE statement to create a .bak file.  The backup files have been about 3.8 GB for months.  Last week on Monday night the backup file was approximately 7.7GB, then it went back down to 3.8 GB every week night for the rest of the week.  Now, again last night (Monday night) the backup file went back up to 7.7GB.  What could cause the backup file to be twice as big on certain nights?
LVL 1
Declan_BasileITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

strickddCommented:
It sounds like you have an incremental backup going that will do an entire backup on a certain schedule and then only backup what has changed until the next full backup.
0
David ToddSenior DBACommented:
Hi,

Is something else running on those nights at the same time as the backup?

A full backup contains all the data, and enough of the log to be consistent. That is, there appears to be a long-running transaction, say an index rebuild that is occurring at the same time, and causing the log portion of the backup to be overly large.

HTH
  David

PS If using SQL 2008, why not turn on the backup compression? When I tested this, the resulting file was 28% of previous size!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
In theory you could just have a VERY large active portion of the log, though that would be a very extreme amount for a db that size.

I suspect the BACKUP did not explicitly specify INIT and so wrote a second backup copy to the same filename as a previous backup copy.

Make sure the BACKUP command specifies INIT.  If it does, then the new backup will completely replace any existing backup file with the same name.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott PletcherSenior DBACommented:
If the BACKUP command does not specify INIT, and a backup file with that name already exists, by default SQL will write the new backup *after* the existing backup, *not* over it.  You must explicitly specify INIT to get SQL to write over an existing backup file.
0
Declan_BasileITAuthor Commented:
Yes, something else was running.  We recently took an image of our server and restored it to another box.  Both boxes were on-line and both boxes were scheduled to run the backup.  They both put backups in the same .bak file, in effect doubling the size of the normal backup.
0
David ToddSenior DBACommented:
Hi,

Thanks for the points.

But after reading Scott's comments, I feel that his more accurately answer your question.

Can I suggest instead a split of points between me and Scott, and suggest a grade of A instead of B?

Thanks
  David
0
Declan_BasileITAuthor Commented:
Sure.  I can do that.  I assigned you the points because your comment about how something else might be running triggered me to think of the other Server and figure out the problem.  But Scott's post had to do with more than one backup set stored in the .bak file so it was related to the problem also.  Do you know how to reassign points after a posting has been closed already?  I sent this question to experts-exchange but if you know how and an tell me that'd be great.
0
David ToddSenior DBACommented:
Hi,

I think you may have done it already - the basic thing is to post a question in community support asking moderators to reopen a closed question so you can reassign points.

When the question is open, select the comments and assign points to relevant comments.

Regards
  David
0
Declan_BasileITAuthor Commented:
Thank you both.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.