Sample Header Ad - 728x90

If I replace images in a table, will existing BLOB data be cleaned up and the database shrink?

1 vote
1 answer
89 views
I recently updated a table in my database by saving small thumbnail images as a byte[] to a varbinary(max) column. This significantly improved the speed of loading thumbnails (previously I was loading them from the file system), but due to an error in my code where the thumbnail images were being resized but not compressed, the images were about 140-160KB in size and created a ton of BLOBs. The images should be about 5 or 6 KB. In testing of my new code, the images should be sized correctly at around 5 or 6 KB. If I iterate through the table and replace all of the byte[] images in the varbinary(max) column, will this clean up all these unnecessary BLOBs? My database understandably grew significantly as a result of my error, and I'm trying to get the database to go back down as its current size is creating issues with our backups. --- What I understand of the BLOB data was that it was stored separately in the database and referenced in the varbinary cell in my table, so I wasn't sure if I replaced that if the associated BLOB data would be deleted. If it is automatically deleted, why wouldn't the database automatically shrink? I am testing it in the Dev Environment, but after the last mistake that I made, I wanted to double check. I saw in SSMS that there are options for Shrinking the Database, so I'm assuming that would be the task I should run after updating all of the image data. Of course there will be many backups throughout that process as well. I'm just trying to avoid any additional issues. I have a database that was backup up as about 50MB and grew to about 3.5GB after my error. I can replace the data with smaller images, but it doesn't seem like that would be enough because the database won't automatically shrink.
Asked by M_Lyons10 (13 rep)
Feb 6, 2025, 07:00 PM
Last activity: Feb 7, 2025, 06:09 AM