Wednesday, January 30, 2019

Split a SQL Server file group into multiple data files

Periodically we are asked how to split an existing file group into multiple data files. The quick answer is that SQL Server does not have a built-in way for you to do that automatically, but you can do it yourself. The process is relatively simple and below are the steps to do it.


There have been many articles in the past that talk about using the ALTER INDEX …. REBUILD option to move objects from one filegroup to another and to "rebalance" that way. This author acknowledges the benefits of that technique, but sometimes the question of "rebalancing" is more driven out of simple "geometry" constraints. For example, if I have a database on a volume that I cannot grow, and I simply want to add new files to the filegroup – but have those files reside on a different volume. Adding the new files is quite simple, but by default, the existing file remains essentially full and there is an imbalance between the old and new files.
High level process for splitting a filegroup into multiple files
  • Add several new empty data files to the file group
  • Cap the new files so they cannot auto grow
  • Empty the original data file
  • Readjust all files so they each have the same amount of free space / re-enable autogrowth
  • Shrink the original datafile to the same size of the new files
This process works well and can be done "online" – that is, the objects in the filegroup can be accessed during the splitting process. You should take into consideration that there could be a lot of I/O during this process. In addition to potential performance impacts, databases that participate in an AlwaysOn Availability Group, database mirroring or even log shipping can also be impacted due to the number of log records that are generated – all of which need to be shipped to the respective secondar(ies).This diagram depicts the intended outcome – to take a filegroup with a single data file in it, and split it into multiple data files.


Step 1: Add new data files to the filegroup
The first step in splitting a filegroup into multiple data files is to add one or more new empty data files to the filegroup. In this example, the desired goal is for the original file in the filegroup to be 1/4th its original size and have a total of 4 files of equal size in the filegroup.
In order to do this, we need to add 3 new data files to the filegroup that are each 1/4th the size of the original data file.
--add (@numfiles-1) files to file group
SELECT @loopcntr = 2;
WHILE @loopcntr <= @numfiles BEGIN
    SELECT @NewLogicalName = @LogicalName + '_' + CAST(@loopcntr as varchar(5))
    SELECT @NewPhysicalName = REPLACE(@PhysicalName , '.mdf', '_' + CAST(@loopcntr as varchar(5))+'.ndf')
    SELECT @sql = 'ALTER DATABASE [' + DB_NAME() + '] ADD FILE ('+ @crlf    +
            'NAME = ' + @NewLogicalName + ',' + @crlf +
            'FILENAME = ' + QUOTENAME(@NewPhysicalName, '''') + ',' + @crlf +
            'SIZE = ' + CAST(@NewFSizeMB as VARCHAR(max)) + 'MB,' + @crlf +
            'MAXSIZE = ' + CAST(@NewFSizeMB as VARCHAR(max)) + 'MB,' + @crlf +
            'FILEGROWTH = 0MB) TO FILEGROUP ' + QUOTENAME(@FileGroupName) +';' + @crlf + @crlf
    PRINT @sql
    exec (@sql)
    SELECT @loopcntr += 1
END
 
Step 2: Disable autogrowth on the new data files
The reason for this will become clear in the next step. In the sample script provided with this article, step 2 was actually done in combination with step 1 by setting the FILEGROWTH parameter to "0MB" in the ALTER DATABASE … ADD FILE command. (see above code segment).
Step 3: "Empty" the original data file
After the new files have been "capped" we are ready to "rebalance". This is done by executing a DBCC SHRINKFILE command on the original data file with the EMPTYFILE option. This will take the data from the "end of the data file" and move it into the 3 newly added data files. Since each of those files have the same free space in them, the proportional fill algorithm will evenly distribute the data from the original file into the three new files.
The filegroup will go from this
to this,
--empty the original file -- which will move data into the new files
SELECT @sql = 'BEGIN TRY' + @crlf +
        'DBCC SHRINKFILE (' + @LogicalName + ', EMPTYFILE)' + @crlf +
        'END TRY' + @crlf +
        'BEGIN CATCH' + @crlf +
        '   IF ERROR_NUMBER() <> 2556 BEGIN' + @crlf +
        '      SELECT ERROR_NUMBER(), ERROR_MESSAGE()' + @crlf +
        '      RAISERROR (''Severe error moving data into new files.  MANUAL cleanup necessary.  Terminating connection...'', 19, 1) WITH LOG' + @crlf +
        '   END' + @crlf +
        'END CATCH' + @crlf + @crlf
PRINT @SQL
exec (@sql)
The reason we disabled autogrowth on the three new files is to prevent the original file from getting "too empty". In this example, we want 4 files of equal size when we're done. If we had not prevented the 3 new files from autogrowing, they would have kept growing until the first file was either empty or until all objects capable of moving had been moved. This would not have left us in a balanced state, but in a state that would have looked something more like this.
Step 4: Re-enable autogrowth and set the size to match for all datafiles
At this point we want to make sure that all the files are set to have the same maximum file size and autogrowth paramters. This is done so that if the files become full and need to autogrow, they will be set to grow at the same amount – thus leaving the same amount of free space in all the files.
--set all files to have a MAXSIZE and enable autogrowth
SELECT @loopcntr = 1;
WHILE @loopcntr <= @numfiles BEGIN
    SELECT @NewLogicalName = CASE @loopcntr WHEN 1 then @LogicalName ELSE @LogicalName + '_' + CAST(@loopcntr as varchar(5)) END
    SELECT @sql = 'ALTER DATABASE [' + DB_NAME() + '] MODIFY FILE ('+ @crlf +
            'NAME = ' + @NewLogicalName + ',' + @crlf +
            'MAXSIZE = ' + @maxsizeMBText + ',' + @crlf +
            'FILEGROWTH = ' +  @maxgrowthMBText + ');' + @crlf + @crlf
    PRINT @sql
    exec (@sql)
    SELECT @loopcntr += 1
END
Step 5: "Shrink" the original data file to match the filesize of the other 3 new files
At this point we can issue another DBCC SHRINKFILE on the first file to shrink the file to be the same size as the other 3 files. The diagram below shows the final state at this point, 4 files of equal size in the filegroup.
--shrink the original file to match the new files size
SELECT @sql = 'BEGIN TRY' + @crlf +
'DBCC SHRINKFILE (' + @LogicalName + ', ' + CAST(@NewFSizeMB as varchar(max))+ ')' + @crlf +
'END TRY' + @crlf +
'BEGIN CATCH' + @crlf +
' IF ERROR_NUMBER() <> 2556 BEGIN' + @crlf +
' SELECT ERROR_NUMBER(), ERROR_MESSAGE()' + @crlf +
' RAISERROR (''Severe error moving data into new files. MANUAL cleanup necessary. Terminating connection...'', 19, 1) WITH LOG' + @crlf +
' END' + @crlf +
'END CATCH' + @crlf
PRINT @SQL
exec (@sql)
All the above steps can purely be done using GUI SQL Server Management Studio tool as well.


For any queries you can email me samiappsdba@gmail.com