Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We run automated weekly backups of our SQL Server. The database in question is configured for Simple Recovery. We backup using Full, not differential.

Recently, we had to re-create one of our tables with data in it (making 2 varchar fields a couple characters longer). This required running a script which created a new table, copied the data over, and then dropped the old. This worked correctly.

Oddly though, our weekly backup files now SHRANK by over 75%! The tables don't have large indexes. All data was copied over correctly (and verified). I've verified that we are doing full and not incremental backups. The new files restore just fine.

I can't seem to figure out why the backup files would have shrank so much? I've also noticed that they get about 10 MB larger every week, even though less than that amount of data is being added.

I'm guessing that I'm simply not understanding something. Any insight would be appreciated.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

If all the data's there, then it's a combination of the transaction logs for the database being committed to the database and deleted from disk, and any "white space" in the database being eliminated. For most databases, when data is deleted, the row is removed, but no new data is inserted into that row afterwards, so you can end up with a substantial amount of "white space" that doesn't contain data, but occupies physical space all the same.

There's a difference between the logical size of a database (the size its data occupies) and the physical size of a database (the size the database file or files occupy), the difference usually being [almost exclusively] a combination of the two things mentioned above.

share|improve this answer
    
Thanks for the reply, but my understanding was that when running a backup, the "whitespace" isn't placed into the backup, so why does it continue to grow at a rate greater than the logical size of the data? –  userx Aug 29 '12 at 17:42
    
It is if you're doing a simple file-level backup. Might help to know exactly what kind of backup you're doing, and with what software. –  HopelessN00b Aug 29 '12 at 20:06
    
Thanks, I think I've found the issue, but confirming. My current guess is that the GHOST CLEANUP process isn't catching up with the volume of deletes and hence when we re-created the table, the indexes were also re-created. (Which is basically what you suggested here, thanks) –  userx Aug 29 '12 at 20:10
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.