sql server misc: Deleted half the rows from a table but it's still the same size -- sql server misc

Deleted half the rows from a table but it's still the same size

pshroads NO[at]SPAM gmail.com
5/11/2006 11:35:56 AM

sql server misc:

We have recently started archiving data in our database. With some of
the tables that I've archived I see a reduction in the physical size of
the table when I delete rows. In other words if I delete 50% of the
rows the amount of disk space the table uses also drop by close to 50%

But there are a few particularly lage table that don't decrease in
size. For example one of our largest tables had 115 million rows
consuming about 35 GB of disk space. After archiving some of the data
we're down to 70 million rows (about a 40% reduction if my poor math
skill are correct) but the table still takes up 35 GB.

I was thinking that this could be due to fragmentation. The table in
question has high extent fragmentation (80-90%) for all the indexes.
Would defragmenting help in this case?

Thanks

Re: Deleted half the rows from a table but it's still the same size

Tibor Karaszi
5/11/2006 10:42:53 PM

First you need to determine whather the interesting part is the reserved extends (which can have
unused pages on them) or if it is pages with little used space. SHOWCONTIG will tell you. In any
case, yes, rebuilding will probably fix, and possibly also reorganizing (depending on what type of
fragmentation you have).

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server misc group.