SQL Server Datafiles

[mohitsahu85]

Hello Experts,

We have ms sql 2008 R2 database with 4 data files each of 900 GB. The database is growing and it has left with only 300 GB free space.

So I would like to take an opinion with you all if

1). It is a good idea to add one more data file of 900 GB or

2). Perform the restructuring of the database and distribute this 4 datafile data to may be 8 new datafile with more space?

If we choose any of the above option, what about the performance and the indexing of the database?

If you have any other option then please suggest. Thanks in advance

Regards,

Mohit

[Former Member]

Hi,

additional datafiles of the same size would be benefitial - in case much growth is still expected.

1st add datafiles, then compress. Data will spread evenly accross all datafiles and access will improve. Study carefully SAP note #1744217 (esp point3.).

brgds,

Eszter

[mohitsahu85]

Hello Eszter,

Thank you for your message.

I would like to be clear myself as this is production system.

If I add 900 GB one more datafile. then the scenario would be

4 old disk having 820 GB each data files

and 1 new disk will be having 0 GB data file.

After this you are stating to compress the database. for this if i take a look on the SAP note (esp on point 3) it states to compress the database tables and not to database all together.

If this is the case, then how the data will get distributed on the all 5 drives.

-----------------------------

Is it possible, if i add new datafile and then compress the database from the sql server with shrinking command?? Then will it distribute the data on all data files or not?

Regards,

Mohit

[Former Member]

No, compression will not distribute the data across all data files. In-fact when you add the new datafile and it will be as large as 900G, it will start writing all new data over to this new file. after some time all your newly modified/created data will be on this datafile and you will start seeing very high I/O on this datafile. Size of your database is pretty big, you will soon start seeing performance problems.