Shrinking SQL2000 Database w 20 datafiles.

[Marc C]

Hello,
I am trying to clean up a database I inherited.

I have an 80GB SQL 2000 database with 20 datafiles each 4096 in size. I
have been able to remove unneeded data and am now trying to clean up.

If I do a Shrink on each datafile would able to recover on average 2gb
out of 4g, however I would prefer to have 10 full datafiles and 10
empty. (or better yet 5 full 8GB datafiles and 15 empty)

Can someone point me in the right direction on how to move the data
around so that dont have 20 partially filled datafiles?

I have noticed that I can shrink a single file and use the "empty the
file option (and move data to other files in the group)." option. I
have already done this to the last 2 datafiles as a test but not sure
how to do this on a large scale. I have also set the 1st 10 datafiles
to be able to grow to 8 GB.

For lack of a better way to say this, Is there a way to defrag or
reorganize the data\tables so everything "moves to the front".

BTW, I have already run a maintenance plan to reorganize the data and
index pages.

[Erland Sommarskog]

Re: Shrinking SQL2000 Database w 20 datafiles.

Marc C (mchang@allosou rce.org) writes:[color=blue]
> I have an 80GB SQL 2000 database with 20 datafiles each 4096 in size. I
> have been able to remove unneeded data and am now trying to clean up.
>
> If I do a Shrink on each datafile would able to recover on average 2gb
> out of 4g, however I would prefer to have 10 full datafiles and 10
> empty. (or better yet 5 full 8GB datafiles and 15 empty)
>
> Can someone point me in the right direction on how to move the data
> around so that dont have 20 partially filled datafiles?
>
> I have noticed that I can shrink a single file and use the "empty the
> file option (and move data to other files in the group)." option. I
> have already done this to the last 2 datafiles as a test but not sure
> how to do this on a large scale. I have also set the 1st 10 datafiles
> to be able to grow to 8 GB.[/color]

I would then proceed to use ALTER DATABASE REMOVE FILE on those
empty files, and continue to remove files in this way, unril the
number of files is more managable.

For what is a manageable number, well, 80 GB is not a frightening
size for a database, so my target number would be 1...

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Danny]

Re: Shrinking SQL2000 Database w 20 datafiles.

I would recommend revisiting your strategy. Having more than one file
indicates you are trying to spread your IO out across more disk. MS also
recommends having at least one file per processor although I don't know how
much effect that really has. SQL Server uses a proportional fill strategy
which means if you have full files and empty files of the same size,,new
data will fill the empty files at an equal rate. This generally causes hot
spots in the IO. Depending on your disk layout or SAN layout either spread
all your IO evenly across all disks or split the filegroups between
functions like data and index.

Preallocate all the space your system will need for as long as you can.
Avoid autogrow. Having it on is a nice safety measure but monitor filegroup
space and ensure that in never is actually needed.

"Marc C" <mchang@allosou rce.org> wrote in message
news:1118784497 .318280.7180@o1 3g2000cwo.googl egroups.com...[color=blue]
> Hello,
> I am trying to clean up a database I inherited.
>
> I have an 80GB SQL 2000 database with 20 datafiles each 4096 in size. I
> have been able to remove unneeded data and am now trying to clean up.
>
> If I do a Shrink on each datafile would able to recover on average 2gb
> out of 4g, however I would prefer to have 10 full datafiles and 10
> empty. (or better yet 5 full 8GB datafiles and 15 empty)
>
> Can someone point me in the right direction on how to move the data
> around so that dont have 20 partially filled datafiles?
>
> I have noticed that I can shrink a single file and use the "empty the
> file option (and move data to other files in the group)." option. I
> have already done this to the last 2 datafiles as a test but not sure
> how to do this on a large scale. I have also set the 1st 10 datafiles
> to be able to grow to 8 GB.
>
> For lack of a better way to say this, Is there a way to defrag or
> reorganize the data\tables so everything "moves to the front".
>
> BTW, I have already run a maintenance plan to reorganize the data and
> index pages.
>[/color]