Skip to Content
author's profile photo Former Member
Former Member

SQL Server add additional data files data not evenly spread

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?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on Nov 21, 2012 at 01:24 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.

  • Posted on Nov 21, 2012 at 09:06 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 21, 2012 at 09:06 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 21, 2012 at 11:14 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.