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!
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/
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!
13 People are following this question.
