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...
> 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
>--Original Message--
>Hi 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...
>> 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
>
>.
>|||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...
> 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
> >--Original Message--
> >Hi 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...
> >> 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
> >
> >
> >.
> >sql