Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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

Your Answer


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.