Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Space is not released after you delete some rows from a table in S


Hi,

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
not released.
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
either,
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
here.
Is there any other alternative solution here and i would be interested in
solution rather than workaround.
Kindly advice!

Thanks,
Shyam

> 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
allocated.

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).

A

-----------------------------------------------Reply-----------------------------------------------

Aaron,

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
used.
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.

Thanks,
Shyam

"Aaron Bertrand [SQL Server MVP]" wrote:

Have a look at these topics in Books Online:

DBCC SHOWCONTIG
-- (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:
DBCC INDEXDEFRAG
DBCC DBREINDEX

-- for SQL Server 2005:
ALTER INDEX
-- particularly the REBUILD / REORGANIZE options

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

"Shyam" <S@discussions.microsoft.com> wrote in message

news:F14BF4C6-B1F2-44DB-9627-7C2ADAC32EF4@microsoft.com...

Shyam,

Is that table a heap (no clustered index)?

AMB

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc