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_
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.
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,
