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?
 
David ToddConnect With a Mentor Senior 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
 
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
 
Scott PletcherConnect With a Mentor Senior 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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.