Export/Import of Database to increase the number of datafiles

[maryjane_steele2]

My BW system is close to 400 GB and it only has 3 datafiles. Since we have 8 CPU cores, I need 8 datafiles. What is the best way to export/import the database in order to achive 8 datafiles?

[Former Member · Accepted solution]

You dont need to go to that much trouble mary.

Simply go into Management studio, right click on your DB, go to properties and files. There you can add the other 5 files. You dont even need to reboot the system.

If you want to make them level out in size, set the 3 existing files to not auto grow and let the other 5 catch up. Once they are roughly within a few GBs you can enable auto growth on all of them again and you should have 8 equally sized files from now on. Since it is a smaller sized BW system you will probably reload enough data to smooth out any clustering inside the files that might exist right now. And all new data will be round robined across all 8 files.

We did a similiar exercise, going from 4 to 8 files, while the system was available to users and the results were successful.

-Jeff

[maryjane_steele2 · Accepted solution]

Jeff, interesting solution. But I have questions. It might take me two years to the 5 new datafiles approx. the same size as the 3 old ones. Everything I've read emphasizes that all datafiles be the same size for performance reasons. I was going to try to split the datfiles using the SHRINKFILE EMPTYFILE but discovered you can't completely empty a .mdf file. So what would the performance ramificatons be of your approach? I'm thinking it wouldn't be too bad because my 8 cores would be using 5 datafiles versus the 3 it is using today.

[Former Member · Accepted solution]

With a BW system that size you can probably get away with it. You most likely do at least a few full loads so all that data will be evenly distrubuted when you drop and reload. If you can clean up some of your PSAs and log tables you can probably shrink the 3 files down a little anyway. If you do little maintenance like that every few weeks, after locking auto growth, you can probably shave 2-3 GBs off each file each week. Do that for a few months and your large files will love 20 GBs while the other ones start to grow. Rebuilding indexes also helps with that. You will be surprised how fast they will level out that way.

With regard to performance you should be fine. I certainly wouldnt do it at 10 am on Tuesday though :-). You can probably get away with it over a weekend though. It will take basically no time at all to create them and very little IO. If you choose to clean out the 3 existing ones that will take some time. I have found it takes about 1-3 hours to shrink a 150 GB datafile down to 100 GBs, that was with 8 CPUs, 30 GBs of RAM, and an SAN that I don't fully understand

[maryjane_steele2 · Accepted solution]

You rock Jeff!

[Former Member]

Hi,

in addition check SAP Note 363018 - File management for SQL Server

C. Adding and removing database files

regards,

kaushal