cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Server add additional data files data not evenly spread

Former Member
0 Kudos

I have a SAP system running on SQL Server 2008 R2. The database was originally setup for 4 data files and 1 log file.

We would like increase the CPUs to 8 and add an additional 4 data files to the database.

The SAP install by default put all data files in 1 filegroup called PRIMARY which is the default filegroup of a SQL Database.

So I will be adding the 4 new data files to the PRIMARY filegroup.

I have read that SQL Server uses proportional fill to fill that data files, so by add the additional 4 data files these will be filled

with most of the data resulting in the data no being spread evenly over all 8 data files.

Is there a SAP recommeded procedure for adding data files and making sure data is evenly distributed over all 8?

Accepted Solutions (1)

Accepted Solutions (1)

xymanuel
Active Participant
0 Kudos

Hi Gareth,

there are plenty of other options to redistribute the old existing data across the new data files without the r3load.

First, create the 4 new Datafiles.

1. Option

Rebuild the clustered index of the biggest tables. This will redistribute the data among all files.

Since you have the online index rebuild option in 2008 r2 and netweaver >=7.0 this should not have heavy impact.

2. Option

Use the SQL Studio to shrink DB File 1-4. If the SQL Server shrinkes the DB File 1, it distribute the pages into the other Files 2-8 (if you have set the traceflag to use even distribution).

The idea is, start a shrink with the goal to empty the file.

This statement will run a long time.

USE [SID]

GO

DBCC SHRINKFILE (N'SIDDATA3' , EMPTYFILE)

GO

During this statement is running check the status of the datafiles:

USE SID

GO

DBCC SHOWFILESTATS;

GO

select percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, * from sys.dm_exec_requests

select * from sys.dm_tran_active_transactions

If you think the Server distributed enough pages, just stop the shrinkstatment by hand.

3. Option

Use MSSCOMPRESS to compress the database, this will also redistribute the data and will save a lot of space. ROW Compression has no CPU Overhead. PAGE compression will have a little CPU Overhead. Nevertheless, DB compression is recommended by SAP and MS.

I always recommend to read and follow the Microsoft SAP on SQL Server Blog of the MSSQL Server development Team: http://blogs.msdn.com/b/saponsqlserver/

4. Option

Best option is the r3load, because this will redistribute the data even over all files. But you need downtime....

Regards

Manuel

Former Member
0 Kudos

I am going to give your Option 1 of rebuilding clustered indexes a try on a test system.

If I am not happy with the out come will definatly go the R3load route.

I really apprecaite all the replies, thanks to all.

Answers (3)

Answers (3)

clas_hortien
Active Contributor
0 Kudos

Hi,

if you want to re-distribute the data over all 7 data files you have to use R3load, as already said in this thread.

If you want, that the NEW data is distributed over the 7 files (and the old data stays in the three original files), you can create the new files so that they contain the same percentage of free space in it. This means, that you have to start with very small files, set a percentage for growing to all files and set traceflag 1117 as explained here.

Best regards

Clas

premsukh_bishnoi
Contributor
0 Kudos

Hi Bosman,

Please go through below link:


http://ecohub.sdn.sap.com/irj/sdn/mss?rid=/library/uuid/4ab89e84-0d01-0010-cda2-82ddc3548c65

(page- 33)--> "Number and Size of SQL Server Data Files"


Please also refer to note 363018 for further information.
Note 363018 - File management for SQL Server

The new data will be written only to this new file until it reaches the
same fill as the other files, as the SQL Server always choose.

If you need to increase the number of data files, the best way is to
completely export and then reimport the database using R3load. This
method ensures an equally distribution of the data for each table over
the data files. However, this is a very time
consuming procedure. You typically only want to reimport the whole
database if it is really necessary. Because an SAP Unicode migration
includes the reimport of the whole database, you could use this event
for adapting the number of data files.

I hope this information helps you.

Kind Regards, Premsukh

premsukh_bishnoi
Contributor
0 Kudos

Hi Bosman,

Please go through below link:


http://ecohub.sdn.sap.com/irj/sdn/mss?rid=/library/uuid/4ab89e84-0d01-0010-cda2-82ddc3548c65

(page- 33)--> "Number and Size of SQL Server Data Files"


Please also refer to note 363018 for further information.
Note 363018 - File management for SQL Server

The new data will be written only to this new file until it reaches the
same fill as the other files, as the SQL Server always choose.

If you need to increase the number of data files, the best way is to
completely export and then reimport the database using R3load. This
method ensures an equally distribution of the data for each table over
the data files. However, this is a very time
consuming procedure. You typically only want to reimport the whole
database if it is really necessary. Because an SAP Unicode migration
includes the reimport of the whole database, you could use this event
for adapting the number of data files.

I hope this information helps you.

Kind Regards, Premsukh

Former Member
0 Kudos

Thank you very much very interesting.

I would still like to know how to do this directly in SQL Server without having to go the R3load route.

R3load does make sense, thanks again.

premsukh_bishnoi
Contributor
0 Kudos

Hi Bosman,

As per my knowledge, this is not possible with out R3load. Either you do export/import or R3load.

Regards, Premsukh