Too big amount of unused space after shrink of image field contents

Question

Too big amount of unused space after shrink of image field contents

A K-143449

SSC Veteran

Points: 217
More actions
May 11, 2004 at 6:23 pm

#161329

We store a big amount of images in our SQL Server DB in column of type 'image' . Now we try to make space used by DB smaller by shrinking the images stored in the image fields.
After completion of the shrinking process I run 'sp_spaceused' and found that the space occupied by table is close to be the same and most of the new free space is specified as unused.
We will never add any new information to this table. Will SQL Server use the unused space of this table for the new data added to the other tables or we have to cause SQL Server to make reallocation? How we can reorganize the unused space?
Thank you very much for any help,
a_k_

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gerardo Talamo SSC Enthusiast Points: 199 More actions · Answer 1

The best thing I found for this problem is to set up a mainatain plan that runs every night. make sure you optimise the database. tick the 'Remove unused space from database files' this really helps. also in propertise tick the 'Auto shrink'

also I found that detaching and reattaching the database does help (don't recommend you do this too often). but if you dettach the database what SQL server does is automatically shrinks the database (reclaims unused space). when you attach it again it will attach it with no unsed space available, thus making it the minimum size again.

A K-143449 SSC Veteran Points: 217 More actions · Answer 2

Thank you for your help. I tried both methods, no one removes unused space created by fragmentation of the image fields. After each attempt I run 'sp_spaceused' with 'updateusage' option for this table and all the values are the same (the same size of unused data for this table) . It seems like all those methods do not move data within space where image data stored.

Is there any method to reorganize image data space?

The only method that I found is to copy all the contents of the table to a new table. I hope it suppose to be some easier way. The table size is more then 100GB.

Thanks,

a_k_

Steven Allen Valued Member Points: 56 More actions · Answer 3

If the table is clustered (has a non-clustered index, then you can try a DBCC DBREINDEX (tablename,'',100). The '' specifies to reindex all indexes on the table, and the 100 specifies to completely fill the data pages when done.

If not, then create a clustered index on any column, specify a fill factor of 100. After creation you can drop the index.

Unfortunately, this process could take a while on a 100GB table.

Tao Klerks SSCarpal Tunnel Points: 4297 More actions · Answer 4

I know this topic is long-dead, but in case someone finds it while searching for LOB unused space issues, the following topics deal with the same issue and offer a work-around for freeing the unused space:

http://www.sqlservercentral.com/Forums/FindPost740006.aspx

http://www.sqlservercentral.com/Forums/FindPost1011086.aspx

http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.