Sql-server – Free space from image column after update

disk-spacesql-server-2008-r2update

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

Setting to 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?

EDIT

Just ran the commands. Database size remains the same. Any advice?

DBCC CLEANTABLE (0, 'myBigTableWithNullImages', 1000);
GO    
DBCC UPDATEUSAGE (0);
GO

Running EXEC sp_spaceused I get following data.

database_size    unallocated space
159115.02 MB        81323.46 MB

Best Answer

Removing data will not release the space used by to the filesystem unless you then perform a database (or per file) shrink. The newly unallocated space will get used by new data as it comes in before any more is claimed from the OS by growing the relevant files.

If you need to fully reclaim the space then you need to look at DBCC SHRINKDATABASE and DBCC SHRINKFILE, though don't do this unless you have to as it can cause internal fragmentation that if allowed to build up can impact performance.

When you take a backup only used pages are included, so you will see backup sizes drop even though the actual database size has not reduced.

Related Question