question

Apeman avatar image
Apeman 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!
administrationshrink-databasefilegroup
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image David Wimbush commented ·
Which version of SQL Server is this? This used to be a real problem with older versions but has improved in more recent versions.
1 Like 1 ·
Oleg avatar image Oleg commented ·
@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
1 Like 1 ·
Apeman avatar image Apeman commented ·
Sorry, SQL 2005 SP3 CU10 Enterprise Edition
0 Likes 0 ·
Apeman avatar image Apeman commented ·
This database is part of Microsoft Axapta v4 (blame them)
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
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.
0 Likes 0 ·

1 Answer

·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
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 :(
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.