Nulling text data

I have a table that contains text data(XML formatted code). We have decided
on a strategy to archive some of this data and null it out. Out of a 220GB
database, this history table is 153GB. After setting the text field to null,
we haven't realized any space improvements. We reindex often. The previous
DBA suggested copying the rows out and renaming the destination table but I'm
sceptical that I can finish that in any conceivable downtime due to the row
count and general query performance when accessing text columns. Any ideas
on how to reclaim this space?On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
On SQL 2005, you could use index defrag to reclaim the space.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
>I have a table that contains text data(XML formatted code). We have
>decided
> on a strategy to archive some of this data and null it out. Out of a 220GB
> database, this history table is 153GB. After setting the text field to
> null,
> we haven't realized any space improvements. We reindex often. The
> previous
> DBA suggested copying the rows out and renaming the destination table but
> I'm
> sceptical that I can finish that in any conceivable downtime due to the
> row
> count and general query performance when accessing text columns. Any
> ideas
> on how to reclaim this space?|||use DBCC SHRINKDATABASE command
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:OMJ0736oFHA.3756@.TK2MSFTNGP09.phx.gbl...
> On SQL 2000, the best thing to do is to bcp out/in to reclaim the space.
> On SQL 2005, you could use index defrag to reclaim the space.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://blogs.msdn.com/weix
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jeffrey K. Ericson" <JeffreyKEricson@.discussions.microsoft.com> wrote in
> message news:BA149A27-1E2F-460A-A798-E4061FCDD4E4@.microsoft.com...
> >I have a table that contains text data(XML formatted code). We have
> >decided
> > on a strategy to archive some of this data and null it out. Out of a
220GB
> > database, this history table is 153GB. After setting the text field to
> > null,
> > we haven't realized any space improvements. We reindex often. The
> > previous
> > DBA suggested copying the rows out and renaming the destination table
but
> > I'm
> > sceptical that I can finish that in any conceivable downtime due to the
> > row
> > count and general query performance when accessing text columns. Any
> > ideas
> > on how to reclaim this space?
>