"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns9870D15503FYazorman@127.0.0.1...
> Zeng (zeng@nononospam.com) writes:
>> I have read at a few places that sql2k cannot claim back the text space
>> used for data, we just moved a database created in sql2k to sql05
>> Enterprise Edition and it still cannot claim back the text space. At one
>> time, the table had 0 row but still held up 12G of disk space.  I
>> thought this problem was fixed in sql05.  Did anybody run into this
>> problem and have a good solution?
>
> I had a table the other day on SQL 2005 that was 3GB, while having no
> rows in it. There was on text columns in this table, though.
>
> I believe that there is a new feature in SQL 2005, so that unused
> extents are deallocated asynchronously. I found my that table was
> slowly shrinking, despite I did not touch it.
>
> Eventually, I performed a TRUNCATE TABLE on it, which released all
> pages.
>
> I then restored a backup with the original state of 3GB and ran
> ALTER INDEX REBUILD on the table. This also brought down the table
> to zero size. However, it seems that a previous run of ALTER INDEX REBUILD
> had expanded the table in size, because a slow-running procedure,
> ran even slower after the rebuild. But I was not around to see what
> actually was going on then. (That procedure uses the table as work
> area, and deletes all rows it has added at the end.)
>
>> Basically I had to drop the table and re-created it to get back the
>> space; the 2 other methods: dbcc shrinkfile and sp_spaceused with
>> updatestatics=true didn't work.  I had many other tables with text field
>> and dropping and re-creating every single one of them periodically would
>> be very painful.
>
> None of these methods can be used to reclaim unused table space.
> UPDATEUSAGE just update values, and I would assume that it's obsolete
> in SQL 2005. SHRINKFILE only deals with unallocated space, that is
> space that has not been allocated to extents.
>
> ALTER INDEX REBUILD or REORGANIZE is what you should use in SQL 2005
> to reduce the size of a single table, if there is a surplus of unused
> space.
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

"Zeng" <zeng@nononospam.com> wrote in message news:uO8jPAu$GHA.1220@TK2MSFTNGP04.phx.gbl...
> Any of these commands ALTER INDEX REBUILD and REORGANIZE can be made into a
> job that I run every night?  By the wya, you mentioned that you also used
> TRUNCATE TABLE but at the end you didn't mention it for sql05, is there a
> reason for that? Thanks!
>
> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message
> news:Xns9870D15503FYazorman@127.0.0.1...
>> Zeng (zeng@nononospam.com) writes:
>>> I have read at a few places that sql2k cannot claim back the text space
>>> used for data, we just moved a database created in sql2k to sql05
>>> Enterprise Edition and it still cannot claim back the text space. At one
>>> time, the table had 0 row but still held up 12G of disk space.  I
>>> thought this problem was fixed in sql05.  Did anybody run into this
>>> problem and have a good solution?
>>
>> I had a table the other day on SQL 2005 that was 3GB, while having no
>> rows in it. There was on text columns in this table, though.
>>
>> I believe that there is a new feature in SQL 2005, so that unused
>> extents are deallocated asynchronously. I found my that table was
>> slowly shrinking, despite I did not touch it.
>>
>> Eventually, I performed a TRUNCATE TABLE on it, which released all
>> pages.
>>
>> I then restored a backup with the original state of 3GB and ran
>> ALTER INDEX REBUILD on the table. This also brought down the table
>> to zero size. However, it seems that a previous run of ALTER INDEX REBUILD
>> had expanded the table in size, because a slow-running procedure,
>> ran even slower after the rebuild. But I was not around to see what
>> actually was going on then. (That procedure uses the table as work
>> area, and deletes all rows it has added at the end.)
>>
>>> Basically I had to drop the table and re-created it to get back the
>>> space; the 2 other methods: dbcc shrinkfile and sp_spaceused with
>>> updatestatics=true didn't work.  I had many other tables with text field
>>> and dropping and re-creating every single one of them periodically would
>>> be very painful.
>>
>> None of these methods can be used to reclaim unused table space.
>> UPDATEUSAGE just update values, and I would assume that it's obsolete
>> in SQL 2005. SHRINKFILE only deals with unallocated space, that is
>> space that has not been allocated to extents.
>>
>> ALTER INDEX REBUILD or REORGANIZE is what you should use in SQL 2005
>> to reduce the size of a single table, if there is a surplus of unused
>> space.
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
>

"Zeng" wrote:

> I have read at a few places that sql2k cannot claim back the text space used
> for data, we just moved a database created in sql2k to sql05 Enterprise
> Edition and it still cannot claim back the text space. At one time, the
> table had 0 row but still held up 12G of disk space.  I thought this problem
> was fixed in sql05.  Did anybody run into this problem and have a good
> solution?
>
> Basically I had to drop the table and re-created it to get back the space;
> the 2 other methods: dbcc shrinkfile and sp_spaceused with
> updatestatics=true didn't work.  I had many other tables with text field and
> dropping and re-creating every single one of them periodically would be very
> painful.
>
> Thank you!
>
>
>