Reduce/reclaim physical database size after deleting image/blob data

Last Post 17 Oct 2012 08:38 AM by gunneyk. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
JeffBenedict
New Member
New Member

--
16 Oct 2012 05:03 PM
I have a database that has 1 table in particular that stores a copy of a signed and populated completed pdf form in an Image column. When the front end application completes it's session, this completed pdf record gets copied to a new table and becomes the most recent "final copy" version for that hire record. Over time, this completed forms table has grown pretty large because each "session" could have say 10 pdf's that could be 1mb each. Now to me, having 20-30 gb of blob "temporary" records sitting out there seems excessive. I can't re-engineer the process, but I can add a step to remove the completed forms from the table once they are persisted to the hire record. But, this doesn't recliam any physical drive space. The DB is currently 165GB of which approx 2/5ths is completed forms, 2/5ths is the persisted "final copy", and 1/5th is normal data. How can I reclaim this 2/5th space after removing the "temporary" records, and how can I keep it "trimmed down" so it doesnt grow back?
I hope all of that made sense to you. THanks in advance.
Jeff
rm
New Member
New Member

--
17 Oct 2012 05:07 AM
You can shrink db file with 'dbcc shrinkfile'. If you cleanup table daily, it shouldn't grow to much unless some day has much more new data get into it.
gunneyk
New Member
New Member

--
17 Oct 2012 08:38 AM
You always want plenty of free space in the database at all times for new data and to do things such as reindex etc. So I don't see any need to shrink the file just to have more space on the disk as you will most likely grow in size regardless of triming the data, just slower. If you periodically delete the old blobs that will allow new blobs to reuse that space and you shouldn't continue to grow as you have in the past. Shrinking a data file with blobs can be extremely slow and resource intensive so I would avoid that if at all possible. Just add the process you mentioned to purge old data and you should be good to go.
You are not authorized to post a reply.

Acceptable Use Policy