Re: reclaim space

From: Stephen Yuan Jiang [MSFT] (syjiang_at_online.microsoft.com)
Date: 02/03/05 

Date: Wed, 2 Feb 2005 18:35:45 -0800

If your text data is small, here is my theory why shrink does not work: SQL
Server shares text data from different rows in the same page (Page type is
TEXT_MIX_PAGE - type 3 from page head). If you remove one row in the
text_mix page (via deleting row or set the text to NULL), the page may not
be empty. For example, you have 200 text records and it fits in 100
text_mixed pages (each page has 2 rows), if you remove 1 row from each page,
each page has 1 row left and no one can use those pages except the same
table (as it is still in use). When shrink comes in and does some estimate,
it finds that all 100 pages are occupied (for performance reason, shrink
just read pfs bit, not read each page - so shrink does not know that it can
compact some of the pages), thinks there is nothing to shrink, and finishes
without touching those pages. You can use undocumented DBCC EXTENTINFO to
verify my theory.

Dropping Clustered index and recreating it won't help you, as SQL Server
will not unload and reload text data (text data may be orphaned in the
middle of the operation, but it is fine, it is protected by the
transaction).

In SQL Server 2000, (from top of my head,) in this situation, you only can
BCP out the data, truncate the table, and BCP in the data - which is
equivent to what you did ("create a table, copy the data, and drop the old
table").

In SQL 2005, SQL Server introduces LOB_COMPACTION feature in ALTER INDEX and
DBCC SHRINKDATABASE/SHRINKFILE. This could solve your problem and reduce
your headache of unloading/reloading data.

Hope this helps you. 

-- 
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Joseph" <Joseph@discussions.microsoft.com> wrote in message
news:B1956D66-35C9-449E-892C-60EB71E350A7@microsoft.com...
> I am having a similar problem except that I am not deleting the rows, only
> updating the text column to null. I tried dropping the clustered index and
> recreating it with no success. I shrunk the database, backed up the t-log
> reshrunk and still nothing to show for my efforts.  The only think that
works
> is creating a new table and copying the data to it and dropping the old
> table. That results in shrinking the table to half of its size.  I am
> stumped. Help please!
> Joseh
>
> "Stephen Yuan Jiang [MSFT]" wrote:
>
> > No, unless you drop the text columns, DBCC CLEANTABLE won't help you.
> >
> > If you want to reclaim space from operation system, in SQL Server 2000,
you
> > can use DBCC SHRINKDATABASE to truncate files in the database.
> >
> > If you want to reuse those space for other tables, then you don't need
to do
> > anything.
> > - Case 1: If the row deletion frees up a page from a uniformed extent,
the
> > page could be re-used for the same table/index.
> > - Case 2: If the row deletion frees up a uniformed extent, the extent
could
> > be re-used by any table/index.
> > - Case 3: If the row deletion frees up a page from a mixed extent, the
page
> > could be re-used by any table/index.
> > With the massive deletion you have, I believe Case 2 is the most likely
> > situation to happen.  You can re-use those space for other tables.
> >
> > -- 
> > Stephen Jiang
> > Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "J Pacquiao" <J Pacquiao@discussions.microsoft.com> wrote in message
> > news:25FDF147-628C-4CC8-825B-837FB396F796@microsoft.com...
> > > I inherited several monster tables with some columns having text
datatype.
> > I
> > > am planning to archive the older data someplace else then delete them
from
> > > the main production server.  How do I reclaim the space?  Do I need to
> > issue
> > > DBCC CLEANTABLE?  BOL states that CLEANTABLE reclaims space for
dropped
> > > columns (variable lenght and text).   Does it also work for row
deletion?
> > >
> > > TIA
> > >
> >
> >
> >

Relevant Pages