Regd. adding new datafile

[Former Member]

Hi All,

We have mapped a drive of 400 GB from SAN to our Production system and all our database data files are residing on it.

Now this drive space is about to fill completely and our IT team added a new additional drive to the system .

Hardware details: (DB server)

32 GB Memory

2 - Quad core processors ( 8 Cores)

Current Datafile number: 4 (One .mdf file and 3 .ndf file - all are in primary group)

For a better performance,

Query 1:

Do I need to add some more datafiles (.ndf) and place it in the new drive ? If yes maximum how many more files I can add for the above hardware configuration to get better performance ?

or

Do I need to move 2 data files from the existing drive to the new drive and keep the total number of data files as 4 itself using detach/attach method of DB?

Query 2:

If I choose the first option of adding new datafiles to the new drive and if my .mdf file can't grow but new .ndf files can grow, is that okay?

or

Is it mandatory that there should be always space available for .mdf file to grow?

Note: Taking downtime for the production system is not a problem. I need a best option to have good performance also if possible to minimize downtime for this activity.

Regards,

Nalla.

[Former Member · Accepted solution]

Hello,

Many best practice guides recommend having half as many datafiles as CPU cores so 4 datafiles is correct for your 8 core server. Each files should have an equal amount of free space to use proportional fill correctly. Depending on your SAN there can be benefits to having the datafiles on separate LUNS and of course there are always benefits to spreading your data over as many drives/spindles as possible for best performance.

Regards,

Michael

[Former Member]

Dear Nikunj,

Thanks a lot for your suggestion. Please confirm whether my understanding is correct.

1. There should be always space for .mdf file to grow in MS Sql server.

2. Better to keep number of datafiles to 4 and extending the drive space whenever required.

3. It is okay even my single datafile size grows beyond 100 GB and keep growing. (Our DB is growing 75 GB per month, so each datafile size can grow upto 250 GB in a year and keep growing. Is it good to have such big datafiles in case of backup/restore situations?)

Please correct me if I am wrong in any of my statement.

Regards,

Nalla.

[Former Member]

> 1. There should be always space for .mdf file to grow in MS Sql server.

Yes.

> 2. Better to keep number of datafiles to 4 and extending the drive space whenever required.

No. You can add datafiles as much as you want.

But on your last case i agree with you on moving two files to another becuase then there is a scope for growing all 4 datafiles together. And keeping databfiles in different groups of physical hard disk definatly gives you performance improvement.

> 3. It is okay even my single datafile size grows beyond 100 GB and keep growing. (Our DB is growing 75 GB per month, so each datafile size can grow upto 250 GB in a year and keep growing. Is it good to have such big datafiles in case of backup/restore situations?)

Yes. You are right the lesser data file size lesser the restoration time incase of datafile damage/corruption.

Regards,

Nikunj Thaker.

[Former Member]

> Current Datafile number: 4 (One .mdf file and 3 .ndf file - all are in primary group)

>

> For a better performance,

> or

>

> Do I need to move 2 data files from the existing drive to the new drive and keep the total number of data files as 4 itself using detach/attach method of DB?

I will recommond this and also in doing this your performance will definatly get increase.

> Query 2:

>

> If I choose the first option of adding new datafiles to the new drive and if my .mdf file can't grow but new .ndf files can grow, is that okay?

>

This will create problem.

> or

>

> Is it mandatory that there should be always space available for .mdf file to grow?

yes you required space to grow your .mdf file.

Regards,

Nikunj Thaker.