I'm doing some cleanup on one of our database for development purposes on a test server. There are a couple of tables with one
IMAGE column. Both tables take roughly 50Gb of space, being the image column the culprit as the the other files are just int, datetime and small size varchar. I ran an update on the tables, using recommendations I found here in SO and other sites, putting the db on simple recovery mode, disabling indexes, setting transaction isolation levet to read uncommited and updating in chunks of 1K rows. Updates takes some times to run, about an hour. Here is my code:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --mytable UPDATE TOP(1000) mytable SET myImgCol = NULL WHERE DATALENGTH(myImgCol) > 0 WHILE @@rowcount > 0 BEGIN UPDATE TOP(1000) mytable SET myImgCol = NULL WHERE DATALENGTH(myImgCol) > 0; END SET TRANSACTION ISOLATION LEVEL READ COMMITTED
NULL I guess is the best way to free that space, correct?
Then, issue is that after runnning the updates the size of the database remains the same size. I guess the space is not freed up inmediately. Found several references to a ghost process that is supposed to be running behind the curtains freeing space but it takes quite long time to regain all the space that was used by the
IMAGE column. Then found recommendations on running
DBCC CLEANTABLE statement to force to free space and afterwards. Also some recommend running
DBCC UPDATEUSAGE at the end of the process to update the reports related to used pages, reserved pages, etc. Yes, I read about the transaction log growing. My plan is to run
DBCC CLEANTABLE by chunks, as recomended on BOL pages and as I've done with the updates.
Running these commands will help me reclaim the space occupied by the image columns?
Just ran the commands. Database size remains the same. Any advice?
DBCC CLEANTABLE (0, 'myBigTableWithNullImages', 1000); GO DBCC UPDATEUSAGE (0); GO
EXEC sp_spaceused I get following data.
database_size unallocated space 159115.02 MB 81323.46 MB