Very Computer

I have an application that has a table that contains multiple small image files.  This table now needs to have it's images archived, but I wish to have the rest of the record remain.  I have set the image field data to 'null', and then proceeded to shrink the database.  No additional free space was reclaimed.

What i am really asking is how can i clean out field data on specific records and have the file size reduced because of less data in the file.  I have tried shrinking etc, but as of yet there is no way to regain that free area!

Hi Russ,

You have to drop the image column and recreate it, or copy the table, drop
the old table and rename the new table with the old table's name (you can
easily generate a script for this with Enterprise Manager|Design Table). It
is the only way to reclaim the space used by image and text columns in SQL
Server 2000. DBCC DBREINDEX and DBCC INDEXDEFRAG won't work sadly enough.
Yukon will have this functionality though.

--
Jacco Schalkwijk
SQL Server MVP

Quote:> I have an application that has a table that contains multiple small image

files.  This table now needs to have it's images archived, but I wish to
have the rest of the record remain.  I have set the image field data to
'null', and then proceeded to shrink the database.  No additional free space
was reclaimed.

Quote:

> What i am really asking is how can i clean out field data on specific

records and have the file size reduced because of less data in the file.  I
have tried shrinking etc, but as of yet there is no way to regain that free
area!

you might want to try to shrink the data/log files instead. try this script:

from   sysfiles
where   status & 0x40 <> 0

from   sysfiles
where   status & 0x40 = 0

note: this is working for a db with a single data file and a single t-log
file.

HTH,
</wqw>
p.s. put WITH NO_INFOMSGS after DBCC if you dont need the extra output

Quote:> I have an application that has a table that contains multiple small image

files.  This table now needs to have it's images archived, but I wish to
have the rest of the record remain.  I have set the image field data to
'null', and then proceeded to shrink the database.  No additional free space
was reclaimed.

Quote:

> What i am really asking is how can i clean out field data on specific

records and have the file size reduced because of less data in the file.  I
have tried shrinking etc, but as of yet there is no way to regain that free
area!

Shrinking does not seem to work.  I am running it thru the enterprise manager.  Would that explain why it doesn't work properly?

Try using the DBCC CLEANTABLE command and see if that helps.

--

Andrew J. Kelly
SQL Server MVP

Quote:> Shrinking does not seem to work.  I am running it thru the enterprise

manager.  Would that explain why it doesn't work properly?

Even if i am not dropping a field, but rather setting the data in the field to NULL?

Sorry about that, I got you confused with a similar posting.  No I don't
believe CLEANTABLE will do anything if your not dropping the column.  I
believe you need to bcp it in and out with the truncate or drop table
in-between to be sure to get it clean.

--

Andrew J. Kelly
SQL Server MVP

Quote:> Even if i am not dropping a field, but rather setting the data in the

field to NULL?