SQL 2008 R2 database backup file size tripled overnight without major changes - what is causing it?

Question

SQL 2008 R2 database backup file size tripled overnight without major changes - what is causing it?

Carmen Vacar

SSC Enthusiast

Points: 106
More actions
February 24, 2012 at 10:34 am

#256423

I have a SQL Server database that I am taking full compressed backups of every day (this is a reporting database that is getting refreshed every 15 minutes from various data sources that feed it). The database is in Simple Recovery mode since it can be recreated from scratch if needed, so logging the changes in a very granular manner is not important. The full backups are native compressed backups. From one day to another, the backup file size tripled without significant changes in the .mdf or .log file sizes. I shrunk the database files (.mdf and .log) for both and I can consistently reproduce the large backup file issue. Has anybody seen this and what could the reason be?
Any help would be greatly appreciated!

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

It seems that either the database backup is no longer being compressed or that the Append option has been selected.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323518 More actions · Answer 2

rebuilding the index requires a lot of space...I've heard a rule of thumb as 1.5x the size of the table.

maybe you rebuilt the indexzes? large overnight ETL?

from MS:

http://msdn.microsoft.com/en-us/library/ms191183.aspx

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!

Carmen Vacar SSC Enthusiast Points: 106 More actions · Answer 3

Thanks for the pointers so far. Here's what I've discovered, but don't know how to address:

There is a new table that seems to be creating the problem. When this table is removed from the database the backup size goes back down to what it used to be. The table has 800K rows and the data size is 150 MB while the index size is 137 MB. It looks as if the compression algorithm used by the engine when it creates the compressed backup does not know how to handle this one table.

Some numbers to illustrate the problem:

Feb13 backup file size: 90 MB

Feb 23 backup file size: 325 MB

The culprit table size (shown above) seems to be making up for hte entire delta in the backup file sizes. Is there a way to resolve this : bring the backup file size down to what it used to be or at least not have it triple with the addition of one table? Why does this table not compress well in the backup? The reason backup file size is important is because we are sending the file across the internet to a partner and the file transfer is very lengthy now that the file size has tripled.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

So this is a brand new table that wasn't there before? Also, keep in mind that some kinds of data won't really compress much or at all, mp3, jpg, gif, etc.

I'm a little surprised the index size is so big, does this table have a lot of indexes? Or are they on columns that won't likely compress well?

Also, 325MB isn't really all that big of a backup file. I thought we were talking real numbers.. Like 10GB to 40GB..

CEWII