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

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?
ThanksFirst you need to determine whather the interesting part is the reserved ext
ends (which can have
unused pages on them) or if it is pages with little used space. SHOWCONTIG w
ill tell you. In any
case, yes, rebuilding will probably fix, and possibly also reorganizing (dep
ending on what type of
fragmentation you have).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<pshroads@.gmail.com> wrote in message news:1147372556.194478.32110@.j73g2000cwa.googlegroups.
com...
> 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
>