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!

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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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" <esquel@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, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx

> 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?

My guess is that your intention is to not delete all the data in the table
every night!

> Any of these commands ALTER INDEX REBUILD and REORGANIZE can be made into a
> job that I run every night?

Yes. Also, pay attentions to the LOB_COMPACTION option in 2005.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/


"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" <esquel@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, esquel@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/pro...ads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>
>

Zeng (zeng@nononospam.com) writes:
> Any of these commands ALTER INDEX REBUILD and REORGANIZE can be made
> into a job that I run every night?

Yes.

> 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?

I was talking about SQL 2005 all the time.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hi Guys,

We are experiencing the same kind of strange behavior over here. We have
also noticed that the DB gets lots of space released back in the background.

We are not able to get a good grip on the problem and solutions.

Have you solve this on your side ?

Thanks
Olivier

"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!
>
>
>