shrink sqlserver

24 Aug 2006 10:13 AM

asp30

I've been using SqlServer 2000 server edition.

my mdf file was 9 GB.

as it was too big according to the ammount of data stored, I tried to:
shrink it, rebuild indexs, etc etc, ... but it's size always ranges
from 8 to 9 GB.

finally I' ve made a copy of the database. I've trunkated some table,
and I've copied data back to these table using DTS.
As a result the database size dropped down to 1,2 GB.

I've tried same thing on several database, and using DTS the size of
the MDF file was allways
cut to less than a half of the original size.

How is it Possible ??
Is there a simplest way to make a so strong shrink of the database as
it causes me truoble with backup ??

Thanks a lot.

Paolo

24 Aug 2006 10:30 AM

Uri Dimant

Hi
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

<as***@libero.it> wrote in message
Show quote

news:1156414416.953884.255550@75g2000cwc.googlegroups.com...
> I've been using SqlServer 2000 server edition.
>
> my mdf file was 9 GB.
>
> as it was too big according to the ammount of data stored, I tried to:
> shrink it, rebuild indexs, etc etc, ... but it's size always ranges
> from 8 to 9 GB.
>
> finally I' ve made a copy of the database. I've trunkated some table,
> and I've copied data back to these table using DTS.
> As a result the database size dropped down to 1,2 GB.
>
> I've tried same thing on several database, and using DTS the size of
> the MDF file was allways
> cut to less than a half of the original size.
>
> How is it Possible ??
> Is there a simplest way to make a so strong shrink of the database as
> it causes me truoble with backup ??
>
> Thanks a lot.
>
> Paolo
>

24 Aug 2006 11:14 AM

Andrew J. Kelly

My guess is that you do not have clustered indexes on all your tables. If
not then an index rebuild will do nothing to the table itself. By exporting
and reimporting you fill the pages back to near 100% full although you may
not reduce logical fragmentation.

--
Andrew J. Kelly SQL MVP

<as***@libero.it> wrote in message

Show quote

news:1156414416.953884.255550@75g2000cwc.googlegroups.com...
> I've been using SqlServer 2000 server edition.
>
> my mdf file was 9 GB.
>
> as it was too big according to the ammount of data stored, I tried to:
> shrink it, rebuild indexs, etc etc, ... but it's size always ranges
> from 8 to 9 GB.
>
> finally I' ve made a copy of the database. I've trunkated some table,
> and I've copied data back to these table using DTS.
> As a result the database size dropped down to 1,2 GB.
>
> I've tried same thing on several database, and using DTS the size of
> the MDF file was allways
> cut to less than a half of the original size.
>
> How is it Possible ??
> Is there a simplest way to make a so strong shrink of the database as
> it causes me truoble with backup ??
>
> Thanks a lot.
>
> Paolo
>

24 Aug 2006 11:48 AM

ML

In addition to the other posts also have a look at this article by Tony
Rogerson:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/22/956.aspx

ML

---
http://milambda.blogspot.com/