Decreasing data file

[Former Member]

Hi,

i have 3 databse files on my sql server 2000 - (ecc6 is installed).

one of the files has the following size - 300GB ,30GB of it are free space.

i have 2 questions - first, can i decrease/shrink the 300GB file in about 20GB on account of the 30GB free space?

second what is the recommended structure for optimized performance - in which size of the datafile - should i add another new datafile ?

Regards,

Moshe

[Former Member]

Hi Moshe,

Q#1:

In principle you could. BUT: please read this thread:

Clas explains there why that might not be a good idea. For more information on fragmentation also check this article:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

Q#2:

You can go by the rule of thumb that one datafile per (processor-) core is recommended. If you add additional datafiles your data is not distributed evenly anymore among those files. Then you might want to think about export your database and reimport the data so that it is distributed evenly again. But before doing so, you should analyze your database to find out if you really have hotspots on certain datafiles.

Regards,

Sven