Recovering space in a heavily fragmented table

I have a large (240GB) table which contains a very large
amount of character data in text datatype columns. During
recent maintenance a good deal of this data has been
nulled to save space resulting in the table reporting
about 80GB of unused space within the allocated extents.
What is the best way to defrag this table and recover the
space?
The table has NO clustered index on it.
Thanks to anyone who thinks they can help!!!
JonathanHi Jonathan,
You can't recover empty extents that have been used by text directly in SQL
Server 2000, but you have two options to work around it:
1) you can copy the data to a new table, drop the old table and then rename
the new table with sp_rename.
2) copy the data in the text column to a new table, drop the text column
from the original table, run DBCC CLEANTABLE on the original table, recreate
the column and copy the data back in and finally drop the new table.
Jacco Schalkwijk
SQL Server MVP
"Jonathan Smith" <jonathan.smith@.moneysupermarket.com> wrote in message
news:48a301c3e40b$f77c7d90$a601280a@.phx.gbl...

quote:

---

> I have a large (240GB) table which contains a very large
> amount of character data in text datatype columns. During
> recent maintenance a good deal of this data has been
> nulled to save space resulting in the table reporting
> about 80GB of unused space within the allocated extents.
> What is the best way to defrag this table and recover the
> space?
> The table has NO clustered index on it.
> Thanks to anyone who thinks they can help!!!
> Jonathan

---

|||Ah. And I thought I was being really stupid and there
would be a simple answer. Thanks for your help. I believe
a long night may be in store for me...
Jonathan Smith

quote:

---

>--Original Message--
>Hi Jonathan,
>You can't recover empty extents that have been used by

---

text directly in SQL

quote:

---

>Server 2000, but you have two options to work around it:
>1) you can copy the data to a new table, drop the old

---

table and then rename

quote:

---

>the new table with sp_rename.
>2) copy the data in the text column to a new table, drop

---

the text column

quote:

---

>from the original table, run DBCC CLEANTABLE on the

---

original table, recreate

quote:

---

>the column and copy the data back in and finally drop the

---

new table.

quote:

---

>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Jonathan Smith" <jonathan.smith@.moneysupermarket.com>

---

wrote in message

quote:

---

>news:48a301c3e40b$f77c7d90$a601280a@.phx.gbl...
During[QUOTE]
the[QUOTE]
>
>.
>

---

|||fyi - this functional shortfall has been fixed in Yukon. Both shrink and
defrag will compact LOB extents.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Smith" <jonathan.smith@.moneysupermarket.com> wrote in message
news:473d01c3e413$c7619ac0$a501280a@.phx.gbl...[QUOTE]
> Ah. And I thought I was being really stupid and there
> would be a simple answer. Thanks for your help. I believe
> a long night may be in store for me...
> Jonathan Smith
>
> text directly in SQL
> table and then rename
> the text column
> original table, recreate
> new table.
> wrote in message
> During
> the