First published on MSDN on Mar 03, 2017 Periodically we are asked how to split an existing filegroup into multiple data files. The quick answer is that SQL Server does not have a built-in way for...
Updated Apr 28, 2020
Version 5.0
Blog Post
I apologize for the reference to my blog that I have ceased to maintain.
Please find below the script that was referenced in the article.
If you have any questions - please feel free to reach out to me at email address removed for privacy reasons
/*=====================================================================
AUTHOR: email address removed for privacy reasons
FILENAME: SplitFileGroup.sql
VERSION: 1.1
THIS SCRIPT USES the CTRL-SHIFT-M macro substitution technique to set several variables:
DBName -- The database in which the filegroup exists that should be split.
FileGroupName -- The name of the filegroup to be split (default = Primary)
FileCountAfterSplit -- The total number of files in the filegroup after the split (default = 4)
MaxFileSize -- The maximum filesize of the files after the split (default=NULL which equals "UNLIMITED")
AutoGrowthSize -- The size for autogrowth after the split is complete (default NULL = 100MB)
OBJECTS CREATED
================
Additional data files added to the specified filegroup.
The number of data files added will be ("FileCountAfterSplit" - 1)
The additional data files are added to same path where the existing data file resides.
DESCRIPTION OF ALGORITHM
=========================
1) use the same path as the existing file.
2) get the size of the current file
3) set the filesize for each of the new files as: (CurrentFileSize/FileCountAfterSplit) + 1MB
4) add (FileCountAfterSplit-1) new files to filegroup
set the Autogrowth value to 0 (so the files cannot autogrow)
set the MAXSIZE = to the value supplied in template variable, or UNLIMITED if "NULL" was used
the "logical" name of each new file is the original file's logical name with an "_x" added to it, where
x = the number of the added file -- starting with "2"
the "physical" name of each new file in the original file's phyical name with an "_x" appended to the filename
where x = the number of the added file -- starting with "2".
the "extension" for the physical files added will be "ndf"
5) issue a DBCC SHRINKFILE with the EMPTYFILE option
Ignore the error "2556". This is expected because not everything can be emptied from the origianl data file.
This is done intentionally so that after the original file is shrunk, all data files will be the same size.
6) iterate through all data files in the filegroup, setting MAXSIZE and FILEGROWTH to the values
provided in the template variables. Defaults being UNLIMITED and 100MB respectively
7) issue a DBCC SHRINKFILE, specifying the new filesize
NOTES:
======
This script is an example script only. It is designed to show one technique on how
to modify a filegroup with a single data to have multiple datafiles and move the
data from the original file into the new files, so that the result is a filegroup balanced
fairly evenly among all of the data files.
This script should not be run in production environments. It is provided for illustrative purposes only
and is provided "AS IS" with no warranties.
This script is designed to split a filegroup that only contains a single datafile.
This script DOES NOT split the PRIMARY filegroup (since some objects can't be moved)
(note--- If you really "need" to split the primary filegroup original datafile
you can do it with the technique in this script/article. Just realize
that because some objects can't be moved, the space allocation afterward my not be
exactly even or uniform).
This script DOES NOT check for adquate file space.
This script DOES NOT do any significant error checking.
This script DOES NOT attempt to handle any "undo" or "rollback" process should an error occur.
If the MaxFileSize specified is less than the calculated new size, the MaxFileSize will be set
to the calculated new size + 10MB.
Depending on the exact amount of used space in the original file, and the number of files
specified to split into, the amount of used space in the original file may be somewhat
less or more than the other files. This script is not designed to ensure that every
file is of exact equal size and used space amounts. All files should be close in
used space though -- if not, please send me an email - I would like to discuss.
REVISION HISTORY:
=================
20170208 1.1 Initial Release
======================================================================*/
SET NOCOUNT ON
USE [<DBName, sysname, SplitFileGroupDB>]
GO
DECLARE @FileGroupName sysname ='<FileGroupName, sysname, Primary>';
DECLARE @numfiles INT = <FileCountAfterSplit, smallint, 4>;
DECLARE @maxsizeMB INT = <MaxFileSize (NULL = unlimited), smallint, NULL>;
DECLARE @maxgrowthMB INT= <AutoGrowthSize (MB) (NULL = 100), smallint, NULL>;
DECLARE @maxsizeMBText VARCHAR(max) = 'UNLIMITED';
DECLARE @maxgrowthMBText VARCHAR(max) = '100MB';
DECLARE @crlf CHAR(2) = CHAR(13)+CHAR(10);
DECLARE @sql VARCHAR(max);
DECLARE @loopcntr INT;
DECLARE @FGFileCount INT;
DECLARE @FSizeMB DECIMAL(19,2), @LogicalName sysname, @PhysicalName sysname, @MaxSize INT;
DECLARE @NewFSizeMB INT, @NewLogicalName sysname, @NewPhysicalName sysname;
IF @maxgrowthMB IS NOT NULL
SELECT @maxgrowthMBText = CAST(@maxgrowthMB as varchar(max)) + 'MB'
SELECT @FGFileCount = COUNT(*) FROM sys.database_files df
INNER JOIN sys.filegroups fg on df.data_space_id = fg.data_space_id
WHERE fg.name = @FileGroupName
IF ((@FileGroupName = 'Primary')) BEGIN
RAISERROR('SplitFileGroup.sql message: This script does not split the primary filegroup. Aborting.', 10,1)
RETURN
END
IF ((@FGFileCount IS NULL) or @FGFileCount = 0) BEGIN
RAISERROR('SplitFileGroup.sql message: Filegroup [%s] does not exist. Aborting.', 10,1, @FileGroupName)
RETURN
END ELSE IF (@FGFileCount > 1) BEGIN
RAISERROR('SplitFileGroup.sql message: The procedure is designed for splitting a filegroup with only 1 file in it. Filegroup [%s] contains <%d> files. Aborting.', 10,1, @FileGroupName, @FGFileCount)
RETURN
END
--if we get here, we have only 1 file in the filegroup --we will proceed
--get the used amount of space and other info from current file in filegroup
SELECT @FSizeMB = (SUM(au.[total_pages])/(1024.*1024))*8192,
@NewFSizeMB = CAST(1+((SUM(au.[total_pages])/(1024.*1024))*8192)/@numfiles as INT)
FROM sys.allocation_units au
INNER JOIN sys.filegroups fg on fg.data_space_id = au.data_space_id
WHERE fg.name = @FileGroupName
IF @maxsizeMB IS NOT NULL BEGIN
IF @maxSizeMB < @NewFSizeMB BEGIN
SELECT @maxSizeMB = @NewFSizeMB + 10
END
SELECT @maxsizeMBText = CAST(@maxsizeMB as varchar(max)) + 'MB'
END
SELECT
@LogicalName = df.[name],
@PhysicalName = df.[physical_name]
FROM sys.database_files df
INNER JOIN sys.filegroups fg on df.data_space_id = fg.data_space_id
WHERE fg.name = @FileGroupName
--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 , RIGHT(@PhysicalName,4), '_' + 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
--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)
--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
--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)