Reclaiming space from large TEXT data

Question

Post reply

Reclaiming space from large TEXT data

Tim OPry

SSCrazy

Points: 2988
More actions
April 11, 2008 at 6:52 am

#74884

We have a table that contains 12 columns, one of which is of TEXT datatype and contains from 1 to 100k+ of HTML data and makes up the majority of the size in the table/database. The remainder of the columns are datetime or varchar under 100 bytes. The TEXT column is also indexed for search purposes.
Once a month, we replace the TEXT (using WRITETEXT) for all records over 90 days old, replacing it with a tag indicating the data was archived. Everything works as expected, except the size of the database or FT catalog does not decrease in size very much (after maintenance which is set to shrink/remove). Judging by the amount of TEXT we have replaced we should see a 20% reduction each month, but instead we have seen less than 5%.
Is there anything else that needs to be done to reclaim space for TEXT data columns after they have been replaced/overwritten with WRITETEXT?

Viewing 9 posts - 1 through 8 (of 8 total)

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

tbeadle SSCommitted Points: 1635 More actions · Answer 1

[font="Arial"]Hello,

Question: Do you NULL the text column first to reset the text pointer?

Regards,

Terry

[/font]

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

Depending on what the data replacement is, you might not reclaim space. I can't remember if text columns share pages or extents, but it's possible that you had 7.5k of text for a column, replace it with 1 byte and there's no reduction in space because the page is still allocated.

Are these TEXT IN ROW, or are they too large?

Tim OPry SSCrazy Points: 2988 More actions · Answer 3

No - I am not setting the column to NULL first - wasn't aware that would make a difference - does it?

Text in Row is NOT enabled - just a default SQL 2000 table, so just the 16 byte ptr is being stored in the ROW. Since performance is not an issue and well over 50% would exceed the ROW capacity, I thought leaving it default (not in Row) was best?

tbeadle SSCommitted Points: 1635 More actions · Answer 4

[font="Arial"]Hello,

I haven't experimented with it but since the text is managed by a pointer, setting it to NULL should reset the field to the minimum length just like a ltrim(rtrim()) would on a varchar column.

Have a great week end.

Regards,

Terry[/font]

Tim OPry SSCrazy Points: 2988 More actions · Answer 5

I found this info on: http://www.mydatabasesupport.com/forums/sqlserver-datawarehouse/167557-reclaim-blob-text-field-reserved-space.html

Which indicates that re-importing is the only way to reclaim the space...ack!

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

Hmmmm... what kind of index did you put on the TEXT column?

[font="Courier New"]CREATE TABLE EraseMe (ID INT, WTD TEXT)

CREATE INDEX Dodah ON EraseMe (WTD)

Server: Msg 1919, Level 16, State 1, Line 1

Column 'WTD'. Cannot create index on a column of text, ntext, or image data type.[/font]

--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)

Tim OPry SSCrazy Points: 2988 More actions · Answer 7

A full-text index to allow searching. (FT Catalog)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

Ok... thanks Tim.

--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)