I just deleted 19k hi-res images from a table column and have a few questions about shrinking

Question

We just moved to a new image-storage method for our application, which means we don't need to store image byte arrays in the database any longer.

To that end, I have deleted (via an UPDATE statement) about 19k byte arrays containing hi-res images (about 8 GB) from a column in a single table. Without the images, I do not expect the database to ever grow back to the size it is now. Again, please note that I did not delete rows here, but have only set field values to NULL.

Other Info:

• The column type was VARBINARY
• There was no index on the column.

Questions

1. Should I just go ahead and shrink the database, then rebuild indexes for that table?
2. Most scenarios I read involve shrinking after a DELETE statement. I didn't delete any rows, I only set the field value to null for all rows. Does this change anything?
3. The column is now deprecated and we may want to just delete it from the table; I believe this will require the table to be recreated. Should I shrink the database before or after I delete the column?

---

Edit: Another possibility just occurred to me. With the image data gone, it might be better to just write a script to completely rebuild the entire database, without the column image column. Thoughts?

Answer 1

If the goal is to make backups smaller, shrinking the data file(s) will not help accomplish that goal in this case, because all of those pages that were holding that max data were LOB pages that should be deallocated (potentially requiring a rebuild).

So unless your drive is literally out of space, I don't see any reason to shrink the file from 8GB, unless you are 100% confident it will never grow that size again.

Note that when you shrink a data file you might actually increase fragmentation which could decrease read performance, impact memory usage, and reduce concurrency. The knee-jerk reaction there is to rebuild indexes, which fixes the fragmentation but increases the file size to make room for the new copies of the index. When the rebuild is done, those pages are freed up, but the data file doesn't magically shrink again, so you're back where you started, and have gained nothing.

If you delete 95% of the data, the backup size will go down, whether you shrink the MDF file(s) or not. An MDF file is just a container; its size does not affect the backup. The actual data affects the backup. More precisely, the number of pages allocated with any data affects the backup (which is why a table rebuild after a big delete might yield a difference in backup size).

This is easy to test for yourself, but keep in mind the impact you observe will depend on a lot of things, including data types, fragmentation, free space, and a host of other variables. Just look at the size of a full backup after each of these steps:

1. before you set the values to NULL.
2. after you set the values to NULL (and after dropping the column, maybe).
3. after rebuilding the table.
4. after shrinking the database.

My theory is that, in almost all cases I can think of, there will be no tangible difference between the backup size after 3. and after 4.

A different variable you might care about is the time it takes to restore that backup on another system, which will be dependent on the size of the MDF file(s) only in the following cases:

1. Instant file initialization is not enabled
2. The destination drive can not accommodate the current size

The first one is easy to fix, this is just a right you can grant to the service account. The second one should be easy to fix too: add disk space. There's no reason to sacrifice your primary system because your secondary system is inadequate.