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.


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.

| 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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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