How to Break an Existing Single Database File Into Multiple Files

By Lori Brown | Performance Tuning

Jul 26

I occasionally come across some pretty good sized databases that are set up with a single data file.  We recently have been working with a client to break up their single data file into multiple data files so that we can spread them over several different LUNs and so that they can take advantage of the improved performance of using the files in parallel.  The concept is much like setting up tempdb with 1 file (up to 8) per core.

Since most people don’t think about using multiple files for databases until they have grown large enough to be a problem, I think that most don’t realize that breaking up a database can be done at any time, you just need to have enough space for new files.  Here is a bit of a demo on how to do this.

Create a database with one file and load it with a couple of large tables

 

As you load data you can seed some known values into the tables at different places while loading up the tables with random values.  This will let you run some test queries if you are interested in them.

 

Here is how the single database file is filled.

 

Now I want to add more files to break the main .mdf  up so that I can spread the files around and take advantage of some nice performance.
Add 3 new files…

 

Here is how space is used in them before moving data to the new files.

Now we run a DBCC SHRINKFILE with EMPTYFILE and it will automatically start distributing data to the new files.

 

This will actually run in the background so if your workload is light, you can run it while users are connected.  Otherwise you will need to do this during a maintenance window when use is light or none at all.  You can also stop and restart this process.  So, if you can only let this go for 5 hours on one day and have to work on it again on another day you can do that but just stopping the above query and starting up again later.

 

I ran into this error at the end of the shrinkfile.  This happens because there is still system info in the first file that cannot be moved and is expected behavior.  Here is how the files are filled after the shrinkfile.  You can see that the new files have data sort of evenly distributed to them.

I decided to shrink the original database file to match the size of the other files so that I would not see any strange usage with one file being larger than all the others.  It is important to try to keep them all the same size.

Now, I loaded more random data to see how the data will be stored.  You can see the proportional fill is working.

I have actually performed this type of project a couple of years ago on a live database that was roughly 1TB.  It took several days and did cause the users to experience some slowness.  So, test things well so that you can know what to expect if you break up your database.

If you are wondering about how performance will be improved, check out this from Paul Randal ( Blog | Twitter ).  The post while a little old is still very good.  https://www.sqlskills.com/blogs/paul/benchmarking-do-multiple-data-files-make-a-difference/

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com.  We will be happy to help!  Leave a comment and feel free to track back to us.  We love to talk tech with anyone in our SQL family!

  • […] Lori Brown shows us how to take a database with one database file and add new database files to it: […]

  • >