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
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.
--add (@numfiles-1) files to file groupSELECT @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 += 1END
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() &lt;&gt; 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
