splitting large sql database files

[former_member193578]

Hi

I have large sap sql server data files having size in 40 GB/ 3 data files. Is there any way to split those data files into smaller size .

Thanks and Regards

Jijeesh

[Former Member · Accepted solution]

Hi Jijeesh,

SAP don't recommend spliting database files in one file (performance issues).

If you want to get back some space go to Enterprise Manager and right click on your database.

Choose All Tasks -> Shrink Database. New window pops up. On the new window click "Files..." button and on the dropdown box (database files) choose your first file.

Compare values "Current size" and "Space used". If you have some space you may shrink that.

Regards,

Marcin

[clas_hortien · Accepted solution]

Hello,

shrinking files is not recommended as the page order will be destroyed through this, which has major impacts on the performance.

If you want to split the database into more files you must run a homogenous systemcopy with r3load. So exporting the database and then import it in again into a database with more files.

Best regards

Clas

[Former Member]

Hello

I do not recommend you try changing the size of your datafiles in the way detailed in this post.

So long as you have followed the general rule "one datafile per core" it does not greatly matter how large the file is.

N.P.C

[former_member185954]

hmm..

i just gave a detailed step cause i did that before safely, however Jijjesh its your wish

Regards,

Siddhesh

[Former Member]

Hello

I would question what it is that you are trying to achieve by splitting the SAPDATA files into a larger number of smaller files. What is the desired outcome or result required? What is the underlying problem?

Performing some of the actions detailed in this thread are likely to cause performance and/or administration problems.

Thanks

N.P.C

[Rudi_Wiesmayr]

What do you mean with "one datafile per core"?

Kind regards, Rudi

[Former Member]

Hi

On a dual core CPU you should configure 2 datafiles.

On a Quad core CPU you should configure 4 datafiles.

Example: Dual core system with 4 CPU = 8 datafiles

Example Quad core system with 4 CPU = 16 datafiles

[Rudi_Wiesmayr]

Is there a written and published rationale for this?

Kind regards, Rudi

[former_member185954]

Yeah

me too want to ask the same question as rudolf , is there a published document for this strategy.

I would surely like to know more about this.

Regards,

Siddhesh

[Former Member]

Hello Rudi & Siddesh,

Yes, there is an official document.

I recommend that all SAP on SQL administrators read it.

http://microsoft-sap.com/doc/SQLServer2005forSAP-BestPracticesTechnicalWhitepaper.doc

You should refer to page 31 "Number and size of SQL Server data files"

The reason for this is related to the number of threads SQL uses to write into the datafiles.

This document details the most common mistakes customers make when using SQL in an SAP environment;

1. Uneven IO due to non-proportional fill

2. Customers doing reorgs (there is almost no reason to do this)

3. Customers using DB shrink (do not ever do this)

4. Customers doing relatively strange procedures such as that documented in this thread (at best this will do nothing, more likely this will cause a IO problem)

Hope this helps

N.P.C

[former_member185954]

Hi N.P.C,

Its a good document and the reason seems compelling enough. However there are few things mentioned in the document worth debating.

Maybe SQL 2005 is good and it spreads its data evenly.. however if we are running huge Databases when I say huge i mean 100s of GBs .. or TBs its advisiable to divide the datafiles for the simple reason that CPU wait time is still okay, but Disk I/O wait time can considerably slow down the system.

Regards,

Siddhesh

[Former Member]

Hi

As per the document and as per the benchmarks that have been done internally - for best performance - one datafile per core - always, no exceptions.

I really cannot debate this topic anymore. The size of the datafile is not the determining factor. There are systems with datafiles well over 100GB each.

[Former Member]

Hi

There is another reason to split the data files, and the number of cpu doesn't matter. The number of arrays and the space avaliable on each. There is not benefits on create many datafiles than cpus ( cores is cool ? 😛 ) but you may have to do it due storage configuration.

Actually, with the moderns processors the bottleneck is in the storage subsystem. It's better to optimize it and one of the best ways is to split the database in datafiles and distribute it in differents raids and controllers.

One question: Why sap didn't split the database in filegroups, using filegroups for the indexes and filegroups for data as in the oracle enviroment ( I know, tablespaces ) ?

Best regards.

[former_member185954]

Hi Jijesh,

There is a way of splitting files without hampering performance, I have done this on a production system without loss of performance.

All data files in MSSQL belong to a FILE Group, suppose there are 4 datafiles in your database each of 4GB, assume all files belong to the same filegroup.

file1.mdf

file2.mdf

file3.mdf

file4.mdf

and you want to split each into 2 GB datafiles here is what you do.

(Before starting Take Complete Offline backup of your database by stopping SAP and performing a full offline backup of all data and log files.

This operation technically could be performed with SAP Up and running , however its safer to keep database idle when doing a reorg, so stop SAP instance and do the following.)

1.We select the file file1.mdf

2.Add 2 new files to the same filegroup as file1.mdf and name them as file1a.mdf and file1b.mdf of size 2GB each.

3.RESTRICT growth on all rest files (file2.mdf, file3.mdf and file4.mdf) by unchecking the Auto grow option.

4. Open the SQL Analyser and give the command:

<b> USE '<SAP SID>' ;

GO

DBCC SHRINKFILE 'file1.mdf' EMPTYFILE;

GO

</b>

The above mentioned commands will empty the contents of 'file1.mdf' and restribute the data to other files in its FILEGROUP.

Now we have restricted growth by TURNING OFF growth on 'file2.mdf' , file3.mdf and file4.mdf.

The command will distribute data to the new files created by us file1a.mdf and file1b.mdf.

When the command has completed, you can safely remove the file1.mdf

5. Perform steps 1-4 for all the remaining files file2.mdf and file3.mdf and file4.mdf.

After doing the above operation run a CHECK DB using DBCC CHECKDB , this will ensure that your database integrity is checked and everything is okay.

Now run Update statistics to ensure that your performance will not be hampered.

Regards,

Siddhesh