MS SQL Server - Shrink database file

Author

Shrink database file

Carl

2005-12-29, 3:23 am

Hi all,

Nice problems in between x-mas and a new year!

I have a database created with a setup program and with a fillfactor
for all indexes on 30%.

So before we discovered this, we imported about 500 MB of data into the
database and it grew to 10 GB - size of databasfile.

After some analysis we discovered the fill factor settings. We changed
that to 90% and ran DBCC INDEXDEFRAG (there is a sample script in
Booksonline that handles all indexes in the database) and after that
DBCC UPDATEUSAGE. This worked out fine. There is less pages, in some
cases up to 45% less for one table.

But the datafile is still 10 GB.

I've tried DBCC SHRINKDATABASE (dbname,,TRUNCATEONL
Y) with no
success...

There is no problem with the logfile.

Is it possible to make the databasefile smaller?

Thanks

Uri Dimant

2005-12-29, 3:23 am

Carl
http://www.karaszi.com/sqlserver/info_dont_shrink.asp

Tibor, if I remember well you have promised to add a SEARCH engine om your
web site :-)))))))))
Happy a new year.

"Carl" <cbe@meteorit.se> wrote in message
news:1135839823.146374.172040@g43g2000cwa.googlegroups.com...
> Hi all,
>
> Nice problems in between x-mas and a new year!
>
> I have a database created with a setup program and with a fillfactor
> for all indexes on 30%.
>
> So before we discovered this, we imported about 500 MB of data into the
> database and it grew to 10 GB - size of databasfile.
>
> After some analysis we discovered the fill factor settings. We changed
> that to 90% and ran DBCC INDEXDEFRAG (there is a sample script in
> Booksonline that handles all indexes in the database) and after that
> DBCC UPDATEUSAGE. This worked out fine. There is less pages, in some
> cases up to 45% less for one table.
>
> But the datafile is still 10 GB.
>
> I've tried DBCC SHRINKDATABASE (dbname,,TRUNCATEONL
Y) with no
> success...
>
> There is no problem with the logfile.
>
> Is it possible to make the databasefile smaller?
>
> Thanks
>

Andrew J. Kelly

2005-12-29, 9:23 am

Carl,

You probably should have used DBCC DBREINDEX instead to get all the pages to
the proper fill factors. INDEXDEFRAG only deals with leaf level pages and
does not always do as good a job in addressing the fill factors as DBREINDEX
might. But what you are going to want to do is this:

1. Run DBCC DBREINDEX on all tables with the proper fill factor specified
2. Run DBCC SHRINKFILE. (Make sure to leave plenty of free space)
3. Run DBCC DBREINDEX again.

The reason you need to run DBREINDEX twice is that the Shrink operation will
fragment your indexes so you need to clean them up one last time.

--
Andrew J. Kelly SQL MVP

"Carl" <cbe@meteorit.se> wrote in message
news:1135839823.146374.172040@g43g2000cwa.googlegroups.com...
> Hi all,
>
> Nice problems in between x-mas and a new year!
>
> I have a database created with a setup program and with a fillfactor
> for all indexes on 30%.
>
> So before we discovered this, we imported about 500 MB of data into the
> database and it grew to 10 GB - size of databasfile.
>
> After some analysis we discovered the fill factor settings. We changed
> that to 90% and ran DBCC INDEXDEFRAG (there is a sample script in
> Booksonline that handles all indexes in the database) and after that
> DBCC UPDATEUSAGE. This worked out fine. There is less pages, in some
> cases up to 45% less for one table.
>
> But the datafile is still 10 GB.
>
> I've tried DBCC SHRINKDATABASE (dbname,,TRUNCATEONL
Y) with no
> success...
>
> There is no problem with the logfile.
>
> Is it possible to make the databasefile smaller?
>
> Thanks
>

Carl

2005-12-30, 9:23 am

Hi,

Thank you so very much for the replies... I've tried them all. And my
databasefile do shrink , but not so much that I wanted. There is
probably something about this DB from the early beginnings that's not
right.

I've deleted all non-clustered indexes, and after doing DBCC DBREINDEX,
DBCC SHRINFILE, DBCC DBREINDEX tha database file shrunk to 8,5 GB. But
still, there are to many bytes (6-7 GB) to much.

I'll keep on trying or else I have to create a new database and copy
all data to that one...

I have created a new DB, for test, and copied all data into that one.
And the size of the databasefile (mdf) is, with all indexes, 1.2 GB.

Happy new year from sweden!

//Carl

Carl

2005-12-30, 9:23 am

more info....

sp_spaceused gave me this:

Original DB
Reserved: 8 397 168 000
Data: 1 576 000 000
index sixe: 19 560 000
unused: 6 801 608 000

Test DB
Reserved: 1 293 312 000
Data: 673 192 000
index sixe: 617 432 000
unused: 2 688 000

/CB

Andrew J. Kelly

2005-12-30, 9:23 am

Do you by anychance have Text or Image columns? If so and they were
fragmented or bloated you can not fix those by reindexing in SQL2000. SQL
2005 addresses that issue though. IF you had BLOB's and dropped them you
can use DBCC CLEANTABLE to clean that up in 2000.

--
Andrew J. Kelly SQL MVP

"Carl" <cbe@meteorit.se> wrote in message
news:1135948968.754248.314620@f14g2000cwb.googlegroups.com...
> Hi,
>
> Thank you so very much for the replies... I've tried them all. And my
> databasefile do shrink , but not so much that I wanted. There is
> probably something about this DB from the early beginnings that's not
> right.
>
> I've deleted all non-clustered indexes, and after doing DBCC DBREINDEX,
> DBCC SHRINFILE, DBCC DBREINDEX tha database file shrunk to 8,5 GB. But
> still, there are to many bytes (6-7 GB) to much.
>
> I'll keep on trying or else I have to create a new database and copy
> all data to that one...
>
> I have created a new DB, for test, and copied all data into that one.
> And the size of the databasefile (mdf) is, with all indexes, 1.2 GB.
>
> Happy new year from sweden!
>
> //Carl
>

Sponsored Links