How to Re org the SQL 2008 database to have equal size data files

[Former Member]

Hello everyone -

I have I/O issue with our production system (SAP ECC 6.0 on Windows 2008 and MSSQL 2008).

Following are my I/0 stats in DB02 (since DB Start)

ECPDATA1 E: Data 25.779 28.053 4.689 1,056.68 36.297 23.326 2.515 45.301 14.43

ECPDATA2 E: Data 23.143 24.979 4.68 593.297 17.971 12.448 1.238 47.663 14.518

ECPDATA3 G: Data 9.17 9.807 3.477 1,018.69 36.144 21.938 2.457 46.434 14.712

ECPDATA4 F: Data 10.985 11.788 2.69 148.512 4.777 3.248 0.314 45.722 15.201

ECPDATA5 F: Data 14.746 16.164 2.676 162.39 6.693 3.679 0.432 44.139 15.491

ECPLOG1 D: Log 5.337 27.081 4.916 26.962 26.264 0.037 1.919 726.928 13.688

ECPLOG2 F: Log 0.755 17.582 0.487 35.472 35.161 0.042 2.637 845.998 13.334

The fourth column is ms/op which is very high and also asymmetrical for all the data files.

Also, The data files are not of equal size

ECPDATA1 106,173

ECPDATA2 59,588

ECPDATA3 105,036

ECPDATA4 14,992

ECPDATA5 16,491

ECPLOG1 1,025

ECPLOG2 3,199

So ECPdata1 and ECPdata3 are about 105 GB while #4 and #5 are 14 GB and 16 GB each. As per SQL best practices, all the Data files should be of equal size to get the best performance.

How do I make the data files with equal sizes ?

Your help is very much appreciated.

Thank you

-TSB

[former_member193399]

TSB,

You can identify the largest and fastest growing tables in tocde DBACOCKPIT and perform DBCC reindex on them. this will spread the data evenly across all the data files. do this for multiple table until you get the data file size equal. Reindex can be done online or offline. Perform this reindex in a test system to gauge the performance issue.

Also you don't need 2 log files unless you are running out of space in the first one's drive and wanted to spread it in different drive temporarily.

Hope this helps..

RT

Edited by: RT on Sep 16, 2011 9:38 AM

[Former Member]

Hi TSB

Please check this links

Increase the Size of a Database (SQL Server Management Studio)

[http://technet.microsoft.com/en-us/library/ms175890.aspx]

Shrink a File (SQL Server Management Studio)

[http://technet.microsoft.com/en-us/library/ms190757.aspx]

I hope these links help you.

[markus_doehr2]

You can use SQL Server Management Studio to equalize their sizes.

Markus

[Former Member]

Hello Markus -

Thank you for your quick reply.

Can you please tell me specifically how to equalise the file sizes in SQL management studio ?

Also, as this is a production system, what precaution need to be taken before changing the file size ?

Thank you again

-TSB

[former_member184473]

Hello,

Check note [363018|http://service.sap.com/sap/support/notes/363018] for further information about how to decrease/increase the database file sizes.

But, keep in mind, to redistribute the data inside each datafile you need to do a system (export/import).

Regards,

Eduardo Rezende

[Former Member]

Hi dudes!

The key here is that you manually grow your datafiles before the autogrown mechanism comes to play; in fact, SAP recommends setting the autogrow just to avoid the hypothetical case that should never arrive in which the DB administrator forgot about the database size and it ran out of space.

Otherwise, if you properly monitor and manage your DB you should always grant that at least 30% (to say) of free space is allocated in your datafiles. If you do so, the SQL Server engine should do the rest, as it follows a proportional filling strategy as you can read in SAP note 1238993.

In case your database is not still proportional, my advise is that you just manually grow the datafiles so that all them are the same size, and so SQL Server will do the rest. If you however need to addresss that immediately, you will need to reorganize your database, which is not just more sensitive and complicate, but also which involves some I/O intensive operations (check SAP note 159316).

Cheers!!

--Jesú