Distributing the data files

[Former Member]

Hi All,

We are working on the redistribution process of data files in SQL level. We knew that by doing the empty files options through shrink we could achieve it. But I would like to know, Is there any things which I need to consider in terms of number of data files(Any Formula to arrive a total number of data files for the better performance in SQL database level).

In my environment, the current database size is 420GB and its running with 24 data files, SAP advised us to minimize the data files because the data files are not in order (Like 1 data file having GB and some having 40GB and few having 90GB) - So, SAP recommended us to maintain the data file size as common and distribute the data equally among the files.

So, wondering the data file size and also number according to our environment (It would be kind if you suggest on it)

Database size :420GB

Database version : SQL 2005

CPU core : 4 cores running and its a Xeon processor

Regards

Vijay

[former_member524429 · Accepted solution]

Hi,

But I would like to know, Is there any things which I need to consider 
in terms of number of data files (Any Formula to arrive a total number of data files
for the better performance in SQL database level).

You can refer Topic "Number and Size of SQL Server Data Files" of this [Best Practice document|http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/4ab89e84-0d01-0010-cda2-82ddc3548c65&overridelayout=true] (Page no.37 to 44 ). I hope you will get all the required information ,which can be considered for File Management in SQL server.

Also you can refer the following SAP Notes to get more information.

Note 987961 - FAQ: SQL Server I/O performance

Note 363018 - File management for SQL Server

Note 1488135 - Database compression for SQL Server

Regards,

Bhavik G. Shroff