April 11, 2008 at 6:52 am
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?
April 11, 2008 at 8:42 am
[font="Arial"]Hello,
Question: Do you NULL the text column first to reset the text pointer?
Regards,
Terry
[/font]
April 11, 2008 at 9:12 am
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?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 11, 2008 at 11:10 am
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?
April 11, 2008 at 1:44 pm
[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]
April 11, 2008 at 4:41 pm
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!
April 11, 2008 at 6:30 pm
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
April 12, 2008 at 6:01 am
A full-text index to allow searching. (FT Catalog)
April 12, 2008 at 10:43 am
Ok... thanks Tim.
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply