Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
What's the result of using sp_spaceused on your db? Mainly cleaning up the space doesn't mean it's returned to the file system. The database files will remain the same size on disk, only the free space inside the file will increase. –  Marian Feb 20 at 9:24
    
Edited my question to add sp_spaceused results. Didn't run this command before all the manipulations. I'm using a script based on sys.databases and sys.master_files to get RowSize and LogSize values. Based on your comment, then nevertheless the db is smaller, it will return same size? There is no way to update those values? –  Yaroslav Feb 20 at 9:29
1  
Yep, you got it right. The db files will remain the same size (the database_zise column) all the time after you do a clean-up. The unallocated space is probably the one that's been released by your pictures, so it will change after a future clean-up operation. –  Marian Feb 20 at 9:44
    
Thanks Marian. As this is a work in progress and I will restore again the original database on the dev server I will run the sp_spaceused before and after to check the differences. But for now I can tell that I'm having good results as I just made a backup and it takes just 17Gb, before was taking about 60Gb. –  Yaroslav Feb 20 at 9:49

1 Answer 1

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.

share|improve this answer
    
Yes, as I commented on the question, when I made a backup from the newly cleanedup database the difference was appreciable. But when I restored back the database, the sp_spaceused didn't changed. I've read a lot about the detriments of shrinkdatabase and shrinkfile on data files. But as this is a dev database, running these commands shouldn't be that harmful, or yes? –  Yaroslav Feb 20 at 12:05
2  
When you restore a database you get back exactly what was backed up: the data blocks are in the same place in the resulting files as are the unallocated blocks from the files when you run the backup. The realy damaging behaviour with shrinking is when people do it regularly, sometimes automatically on schedule. That over time quickly creates a lot of internal fragmentation. –  David Spillett Feb 20 at 14:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.