I delete some rows from a table in Microsoft SQL Server 2000, and the result
of the sp_spaceused stored procedure shows that lots of space in the table is
I went thru the Article ID:934378 related to Space is not released after you
delete some rows from a table in S2K and the workaround suggested is to
1. Rebuild the index of the table , or
2. Add a row and then delete the same.
I cannot opt for option 1 here and i do not see the second option meaningful
Is there any other alternative solution here and i would be interested in
solution rather than workaround.
> I cannot opt for option 1 here
Why? You can't reorganize the data in the table without reorganizing the
data in the table.
Think about your house. If you want to rent out 300 sq ft of space to a
college student, you can't take 50 ft here, 30 ft there, etc. e.g. move a
chair from the living
room, a couch from the family room, reorganize a closet, take the leaf out
of the dining room table... and suddenly there is 300ft of space for the
student, but he/she can't even fit into many of the segments that you've
Data in SQL Server is stored on pages (think of these as "rooms"). If you
have 40 rows on a page and you delet 20 of them, the only way to reuse that
space is to go to different pages and move 20 rows over to this page. And
when you keep doing that enough, you will free up a bunch of pages. SQL
Server will not do that automatically, because it is often the case that new
inserts will simply fill in those spots that you removed... and that is less
expensive than a reorganization (even though in the long run it could lead
to decreased performance).
From the message it is clear that i've to check the no.of pages used before
deletion and reorganize the data on the pages to minimize the no.of pages
So what is the best query to check the no.of pages used and what can be used
to reorganize the data on a page,so that my table is in a more organized
manner and under control size.
"Aaron Bertrand [SQL Server MVP]" wrote:
Have a look at these topics in Books Online:
-- (this will show you the page usage, fill %, fragmentation level, etc.)
Now, to actually do something about it, you didn't specify which version of
SQL Server you are using, and like in many cases, this information is
important, since there exists a "better" way to do it in 2005. (Though I am
stil not clear why you "cannot opt for" rebuilding the index.)
-- for SQL Server 2000:
-- for SQL Server 2005:
-- particularly the REBUILD / REORGANIZE options
SQL Server MVP
@discussions.microsoft.com> wrote in message
Is that table a heap (no clustered index)?