asked
LOB data not released?
I ran a script which deleted a lot of rows in a table. This table contains an [IMAGE] column, which is stored in a different filegroup. After deleting these rows, I was expecting to shink the datafile with the[IMAGE] data, but it seems the deleted LOB data is not released, I cannot shrink this datafile. Note: the deleted rows were moved to a temporary table, this table was exported with BCP. The size of this BCP file indicates [IMAGE] data is included. Thanks!
Which version of SQL Server is this? This used to be a real problem with older versions but has improved in more recent versions.
@David Wimbush From the question text it looks like it is SQL Server 2000. Otherwise, why would anyone use the **image** instead of the more appropriate **varbinary(max)** data type? Image is on the [deprecated features list][1] along with text and ntext. [1]:
http://msdn.microsoft.com/en-us/library/ms143729.aspx
Oh, don't remind me. Bane of my life - I'm still supporting an even older Axapta instance, hence my current knowledge of SQL 2000 SP3a. Grr.
After searching on the internet an trying the answers (REORGANIZE WITH LOB_COMPACTION or DBCC CLEANTABLE, but didn't work) it seems the only way to release this data is to drop/create the table :(
