|
|
Posted by Danny on 06/15/05 15:45
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@allosource.org> wrote in message
news:1118784497.318280.7180@o13g2000cwo.googlegroups.com...
> 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.
>
[Back to original message]
|