Can't remove BLOB data from datafiles

Question

Post reply

Can't remove BLOB data from datafiles

Fandy08

Old Hand

Points: 336
More actions
February 25, 2015 at 11:32 pm

#295571

Hi all,
I have a huge amount of BLOB data on an SQL Server 2008 DB (10.0.5500), in a table with an image datatype column. I need to selectively delete it from datafiles using a where filter on the table (and make them disappear from datafile).
I tried to use UPDATETEXT dbo.TBL_EMAILOUTBOUND_DSC.DESC_RAWDATA @ptrval 0 NULL;
loading the pointer with a previous filtered select, according documentation it should work, but it's not true. Do I make any mistake or forget something? If you need I can show the hole script.
Thank you in advance
Andrea

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

On SQL Server 2008 you can manipulate the image data just like any other data type, consider this example

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.MY_BLOB') IS NOT NULL DROP TABLE dbo.MY_BLOB;

CREATE TABLE dbo.MY_BLOB

(

MB_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_MY_BLOB_MB_ID PRIMARY KEY CLUSTERED

,MB_KEY CHAR(1) NOT NULL

,MB_DTS DATETIME NOT NULL CONSTRAINT DFLT_DBO_MY_BLOB_MB_DTS DEFAULT (GETDATE())

,MB_IMAGE IMAGE NULL

);

INSERT INTO dbo.MY_BLOB(MB_KEY, MB_IMAGE)

VALUES

('0', CONVERT(VARBINARY(MAX),REPLICATE('0',8000)))

,('1', CONVERT(VARBINARY(MAX),REPLICATE('1',8000)))

,('2', CONVERT(VARBINARY(MAX),REPLICATE('2',8000)))

;

SELECT * FROM dbo.MY_BLOB;

UPDATE dbo.MY_BLOB

SET MB_IMAGE = 0x00

WHERE MB_KEY = '1';

SELECT * FROM dbo.MY_BLOB;

UPDATE dbo.MY_BLOB

SET MB_IMAGE = NULL

WHERE MB_KEY = '0';

SELECT * FROM dbo.MY_BLOB;

Partial results

MB_ID MB_KEY MB_DTS MB_IMAGE

----------- ------ ----------------------- -----------------

1 0 2015-02-26 06:54:20.983 0x303030303030303

2 1 2015-02-26 06:54:20.983 0x313131313131313

3 2 2015-02-26 06:54:20.983 0x323232323232323

MB_ID MB_KEY MB_DTS MB_IMAGE

----------- ------ ----------------------- -----------------

1 0 2015-02-26 06:54:20.983 0x303030303030303

2 1 2015-02-26 06:54:20.983 0x00

3 2 2015-02-26 06:54:20.983 0x323232323232323

MB_ID MB_KEY MB_DTS MB_IMAGE

----------- ------ ----------------------- -----------------

1 0 2015-02-26 06:54:20.983 NULL

2 1 2015-02-26 06:54:20.983 0x00

3 2 2015-02-26 06:54:20.983 0x323232323232323

Fandy08 Old Hand Points: 336 More actions · Answer 2

This is my situation

table_name index_idtypetype_desc total_pagesused_pagesdata_pages

TBL_EMAILOUTBOUND_DSC11IN_ROW_DATA678967796745

TBL_EMAILOUTBOUND_DSC12LOB_DATA 12944095129430440

When I delete some rows on the table or when I update some image column to null value or anything else the LOB_DATA remains unchanged in the DB.

I tried to remove it with

- index rebuild

- index reorganize with (lob_compaction=on)

- dbcc cleantable

- dbcc updateusage(0)

- dbcc shrinkfile

nothing to do

Drop/create of image column followed by dbcc cleantable erases all LOB_DATA from DB, but I need to cancel only LOB_DATA of table records deleted, not all table data.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Is it a heap table? There's a well known issue with deleting from heaps without the appropriate locks. Use WITH TABLOCK in the DELETE statement to work around it. Or, create a clustered index.

If it is not a heap, after the DELETE has finished, take a backup and then restore WITH REPLACE. Backup copies just the used pages and then restores without the wasted space.

BTW, image is an old datatype: you should use varbinary instead.

-- Gianluca Sartori

Fandy08 Old Hand Points: 336 More actions · Answer 4

I made some test with varbinary(max) datatype (instead of image) and the following sequence works, LOB data is released and canceled from database.

So the problem is limited to (deprecated) image datatype.

1) UPDATE tbl SET imagecol = NULL for all images you want to remove.

2) Run sp_spaceused to note the current space usage.

3) ALTER INDEX ALL ON tbl REORGANIZE on the table(s) in question.

4) Run sp_spaceused to compare.

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Fandy08 (2/27/2015)
I made some test with varbinary(max) datatype (instead of image) and the following sequence works, LOB data is released and canceled from database.
So the problem is limited to (deprecated) image datatype.
1) UPDATE tbl SET imagecol = NULL for all images you want to remove.
2) Run sp_spaceused to note the current space usage.
3) ALTER INDEX ALL ON tbl REORGANIZE on the table(s) in question.
4) Run sp_spaceused to compare.

Great! Thanks for the feedback

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

Fandy08 (2/27/2015)
I made some test with varbinary(max) datatype (instead of image) and the following sequence works, LOB data is released and canceled from database.
So the problem is limited to (deprecated) image datatype.
1) UPDATE tbl SET imagecol = NULL for all images you want to remove.
2) Run sp_spaceused to note the current space usage.
3) ALTER INDEX ALL ON tbl REORGANIZE on the table(s) in question.
4) Run sp_spaceused to compare.

As a bit of a sidebar, if it's a big table, mind the log file because REORGANIZE is fully logged no matter the Recovery Model.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)