question

smorgan_bkep avatar image
smorgan_bkep asked

Large database of images; how to shrink and reclaim disk space?

I have a database that has heavy usage of stored image files; all images stored are at their original size and weren't compressed when saved to their respective tables. The database is several years old and as a result the physical size of the DB is now over 1.2TB. I have since introduced image compression into the application that operates on this DB so now any image stored is compressed before being written to the DB. Additionally, I've ran a utility through the entire DB that made sure all images are compressed. The system administrators now would like to reclaim as much disk space as possible. I've read a lot against shrinking databases, but in this case, it seems like the effort would be well worth getting so much disk space back. Can anyone recommend a good approach for this? Thanks in advance!
shrink-databasecompressionimages
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
SirSQL avatar image
SirSQL answered
My personal recommendation (although it will temporarily require additional space) is to create a new filegroup and then perform a rebuild of the tables and lob data on to that filegroup. Alternatively, create new tables to hold the data on that new filegroup and insert the data into there. Once you have everything moved over you can drop the old tables and rename the new. Once that is done you will have pretty much everything out of the primary filegroup (assuming that is where everything resides) and so you will be able to easily shrink the file and then reindex any tables that remain there. Bob Pusateri did a [good write up of this kind of data movement in a blog post][1] which would probably be a good read for you. [1]: http://www.bobpusateri.com/archive/2013/03/moving-a-database-to-new-storage-with-no-downtime/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

smorgan_bkep avatar image smorgan_bkep commented ·
That is a fantastic approach! I had not thought of that but it makes perfect sense. I will pursue that approach and post back my results. Thanks again for your help!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.