May 12, 2004 at 10:25 am
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_
May 12, 2004 at 3:39 pm
I've never dealt with image data types, but assuming they are stored similarly to "normal" data, you would reindex the table to reorganize it. If you have a clustered index on the table, reindex that, if not, create one, then you can delete it.
Steve
May 13, 2004 at 11:00 am
Image data type stored differently then normal data. They stored "outside a data row. The data row contains only a 16-byte text pointer that points to the root node of a tree built of internal pointers that map the pages in which the string fragments are stored outside a data row. The data row contains only a 16-byte text pointer that points to the root node of a tree built of internal pointers that map the pages in which the string fragments are stored." (Microsoft help).
So, reindex cannot help. I have clustered index for this table and I reindexed the table. It does not help.
The average size of images stored in the table before shrink - 100Kb, after shrink 65Kb.
Is there any option to reallocate or reorganize the 'image' data?
Thank you,
a_k_
May 13, 2004 at 4:09 pm
What version of SQL, if 7 this is a known problem. We actually had the DB grown 2 or 3 times it's before size. Even after deleting a large amount of the data.
I spent a fair amount of time developing a process that would recreate it for MS. Basically they said that it was not worth it to fix it on 7, move to 2k.
KlK
May 13, 2004 at 5:32 pm
We have SQL Server 2000. Does MS have new reallocation options for 2k?
a_k_
May 14, 2004 at 7:14 am
They indicated it was already resolved in 2000. We have not seen the problem since moving, and I was unable to recreate it there.
KlK
May 14, 2004 at 4:18 pm
Do you have image type fields in your database? Or space/allocation problem was with regular fields?
a_k_
May 15, 2004 at 7:39 am
Since I've never dealt with image data, this is just an "uneducated guess", but... what if you DTS'd or otherwise transferred the image data to a new database, then drop the image column in your original, reindex, then add the image data back in????
Steve
May 17, 2004 at 11:00 am
Thank you, it may help to delete the image column and rebuild it. I found also that if I copy all the contents of the table to a new table, the image space of the new table does not have fragmentation. But the table size is more then 100GB.
Is there any easier way available? Some database internal option to remove image space fragmentation?
Thank you,
a_k_
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply