Sql-server – Splitting a DB into multiple files

sql serversql server 2014

I'm managing a 1.5Tb DB that is growing at a rate of ~37Gb/Week. I have been asked to split the DB into 500Gb files, but I cannot use Filegroups due to restrictions from the vendor.

My thinking was to create 3 devices and use the DBCC SHRINKFILE command to equally spread the DB across the devices. Could anyone comment on best practices? Any pit falls to my process?

Best Answer

You really do not have much choice because how proportional fill works in SQL Server.

Proportional fill and how to check whether it works

I am assuming you currently have one file in primary filegroup.

  1. You can rebuild objects with clustered index and those will go to new files totally because of more free space in newly created tables. As your current file start freeing up space you might not be able to move to new files any more (depending on free space in each file).
  2. With DBCC SHRINKFILE only way this can work is with EMPTYFILE option because you cannot shrink smaller than your current used space. If you try to shrink with smaller size it will not work. Once you shrink with EMPTYFILE option current file will only hold some system objects and rest will be split over to your 3 new files.

Next problem will be subsequent data inserts will use the old file (currently emptied) because of proportional fill algorithm.

Here is an article by Paul Randal on the same topic.

Related Question