September 10, 2008 at 6:50 am
I have a DB that is 100GB, set to Full Recovery Mode, compatibility mode 80, SQL 2005 Enterprise.
I wanted to remove some space from the DB. In the past, we have run an archiver process on various tables that are FULL of HTML TEXT columns. This process removes the enormous HTML text, and replaces it with a placeholder that just says "Field contents Replaced 9/9/2008" and such.
I thought that since we replaced full text fields in these rows with much smaller text fields, we would be able to shrink a good deal of the DB, but no dice! Is my thinking on this completely wrong, or is there another step that needs to take place?
September 10, 2008 at 7:11 am
Text data type stores in an odd way. I don't think it shrinks once it's been allocated.
Edit: varchar(max) works much better, but you can't use that in compat(80).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 10, 2008 at 7:17 am
I have the same issue, and posted a question a while ago:-
http://www.sqlservercentral.com/Forums/Topic534396-9-1.aspx
There is a link to some MS articles about the problem, but the solutions are painful!
September 10, 2008 at 7:49 am
That seems to shed some light on the situation.
I haven't been here more than a few months, and I was told that previously, this archiving/text field replacement process cleared up a huge amount of space on the DB (after shrinking post-process), but that appears to be incorrect?
September 10, 2008 at 8:00 am
Ian Scarlett (9/10/2008)
I have the same issue, and posted a question a while ago:-http://www.sqlservercentral.com/Forums/Topic534396-9-1.aspx
There is a link to some MS articles about the problem, but the solutions are painful!
That article pertains to SQL 2000. I'm using 2005, although in compatibility mode 80....does that mean I'm defacto operating under 2000 parameters?
September 10, 2008 at 8:14 am
That article pertains to SQL 2000. I'm using 2005, although in compatibility mode 80....does that mean I'm defacto operating under 2000 parameters?
Sorry, I'm still stuck on SQL 2000 with this database.
I doubt the compatibility mode will affect something like this, so I would guess this "feature" is still present in SQL 2005.
September 10, 2008 at 12:30 pm
In compatibility 80, you're very specifically operating the database as if it were on SQL 2000. That's the whole point of the compatibility level.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply