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!

asked Feb 15 '11 at 07:38 AM in Default

Apeman
311 ● 11 ● 12 ● 15

Which version of SQL Server is this? This used to be a real problem with older versions but has improved in more recent versions.

Feb 15 '11 at 08:00 AM David Wimbush

@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 along with text and ntext.

Feb 15 '11 at 08:09 AM Oleg

Sorry, SQL 2005 SP3 CU10 Enterprise Edition

Feb 15 '11 at 12:11 PM Apeman

This database is part of Microsoft Axapta v4 (blame them)

Feb 15 '11 at 12:12 PM Apeman

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.

Feb 15 '11 at 02:28 PM ThomasRushton ♦

(comments are locked)

1 answer:

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 :(

answered Feb 16 '11 at 02:42 AM