SQL2000 Max datafile size & placement for performance

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

krishna - 08 Jul 2008 01:52 GMT

Hi

environment sqlserver 2000 SP4 enterprise
SAN -Virtual Array storage

I have a database which has 3 datafiles under one filegroup(not on
primary) and one log file. two of those data file size is around 30GB
each and one data file i am using for nonclusterd indexes which is 20GB.
everything on the san same volume. Log file is on different volume. The
data files are configured autogrow and all the 3 datafiles are under one
filegroup

1.I am thinking of adding datafile to this database because of managing
the datafile size, what is the optimal managable file size for 32 bit
windows 2003?

2. if i want to add a data file, the initial size of the new file should
be 30GB ?

3. Placing one of the data file in another volume will give performance
gain, instead of on same drive.

appreciate your valuable suggestions

thanks

Reply to this Message

Linchi Shea - 08 Jul 2008 02:34 GMT

If all three data files are in a single filegroup, you can't specify one file
for nonclustered indexes. I'm not sure there is an optimal manageable file
size. That depends on your environment. On one hand, you can go much larger
than 30GB for a data file. On the other hand, you probably don't want to go
over 200~400GB or whatever your typical LUN size because it would then make
moving the files difficult.

Linchi

> Hi
>
[quoted text clipped - 21 lines]
>
> thanks

Reply to this Message

krishna - 08 Jul 2008 05:22 GMT

thanks linchi

> If all three data files are in a single filegroup, you can't specify one
> file
[quoted text clipped - 34 lines]
>>
>> thanks

Reply to this Message