x

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!
more ▼

asked Feb 15, 2011 at 07:38 AM in Default

Apeman gravatar image

Apeman
311 13 13 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, 2011 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][1] along with text and ntext.

[1]: http://msdn.microsoft.com/en-us/library/ms143729.aspx
Feb 15, 2011 at 08:09 AM Oleg
Sorry, SQL 2005 SP3 CU10 Enterprise Edition
Feb 15, 2011 at 12:11 PM Apeman
This database is part of Microsoft Axapta v4 (blame them)
Feb 15, 2011 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, 2011 at 02:28 PM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort newest
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 :(
more ▼

answered Feb 16, 2011 at 02:42 AM

Wilfred van Dijk gravatar image

Wilfred van Dijk
1.3k 20 25 31

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x126
x38
x32

asked: Feb 15, 2011 at 07:38 AM

Seen: 1428 times

Last Updated: Feb 15, 2011 at 07:38 AM