Removing a filegroup that contains data.

Occasionally, you may find you have an extra filegroup that is no longer required for whatever reason. In order to remove that filegroup, you must move all data from that filegroup to a new filegroup. How do you do that? Let’s take a look.

First, you can manually do this for each table using the GUI. This tends to be problematic, and in many cases this can cause SSMS to do things “under the covers” which may be a surprise, such as dropping and recreating the table. Inevitably, there needs to be a better, more controlled way of doing this, especially when we’re talking about a lot of items that need to be moved.

I tend to do this kind of thing in an automated way.

I’ve written a temporary stored procedure I use to move tables and indexes. This code has the ability to move heaps (tables with no clustered index), clustered indexes, non-clustered indexes, XML indexes, and spatial indexes. This code does not handle partitioned tables and indexes.

This is the code:

/*
	Creates a temporary procedure to facilitate moving heaps, clustered indexes, 
	and non-clustered indexes from one filegroup to another.
*/
GO
CREATE PROCEDURE dbo.MoveDataToCustomFilegroups
(
	@IndexType INT
	, @OldFileGroupName SYSNAME
	, @NewFileGroupName SYSNAME
)
AS
BEGIN
	/*
		Description:	MOVE ALL INDEXES of specified @IndexType from @OldFileGroupName 
				to @NewFileGroupName
		By:		Max Vernon
		Date:		2016-04-21
		NOTE:		Does not deal with partitioned indexes; only indexes that are 
				currently defined as located on @OldFileGroupName are moved.
	*/
	SET NOCOUNT ON;
	DECLARE @OnlineOption BIT;
	DECLARE @msg NVARCHAR(255);
	DECLARE @cmd NVARCHAR(MAX);
	/*
	@IndexType can be
		0 = Heap
		1 = Clustered
		2 = Nonclustered
		3 = XML
		4 = Spatial
	*/
	SET @cmd = '';
	SET @msg = '';
	SET @OnlineOption = 0;
	IF EXISTS (SELECT name FROM sys.data_spaces WHERE name = @OldFileGroupName)
	BEGIN
		IF EXISTS (SELECT name FROM sys.data_spaces WHERE name = @NewFileGroupName)
		BEGIN
			IF @IndexType = 0 
			BEGIN
				/*
					Heaps require building a clustered index on the target filegroup, 
					then dropping the clustered index.
				*/
				SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) END + 
				'CREATE CLUSTERED INDEX [CX_' + o.name + '_' + (SELECT TOP(1) col_c.name FROM sys.columns col_c WHERE col_c.object_id = o.object_id ORDER BY col_c.column_id) + '] ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ' (' + QUOTENAME((SELECT TOP(1) col_c.name FROM sys.columns col_c WHERE col_c.object_id = o.object_id ORDER BY col_c.column_id)) + ') 
	WITH (FILLFACTOR=100, DATA_COMPRESSION=PAGE) ON ' + QUOTENAME(@NewFileGroupName) + ';
	DROP INDEX [CX_' + o.name + '_' + (SELECT TOP(1) col_c.name FROM sys.columns col_c WHERE col_c.object_id = o.object_id ORDER BY col_c.column_id) + '] ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ';
	'
				FROM sys.indexes i
					INNER JOIN sys.objects o on i.object_id = o.object_id
					INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
					INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
					INNER JOIN sys.partitions p ON o.object_id = p.object_id AND i.index_id = p.index_id
				WHERE i.type = 0 /* HEAP */
					AND o.type = 'U' /* USER_TABLE */
					AND o.is_ms_shipped = 0
					AND ds.name = @OldFileGroupName;
			END
			ELSE
			BEGIN
				SELECT @cmd = @cmd + CASE WHEN @cmd = '' THEN '' ELSE CHAR(13) + CHAR(10) END +
			
					CASE WHEN i.is_primary_key = 1 AND i.type = 2
					THEN '
	BEGIN TRANSACTION
	BEGIN TRY
		ALTER TABLE ' + QUOTENAME(s.name) + '.'+ QUOTENAME(o.name) + '
		DROP CONSTRAINT ' + QUOTENAME(i.name) + ';
		ALTER TABLE ' + QUOTENAME(s.name) + '.'+ QUOTENAME(o.name) + '
		ADD CONSTRAINT ' + QUOTENAME(i.name) + ' PRIMARY KEY NONCLUSTERED ('
					ELSE
						'CREATE ' + CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + i.type_desc + ' INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + 
						'(' 
					END + 
						(
						SELECT STUFF((
							SELECT ', ' + QUOTENAME(col_c.name) + CASE WHEN col_ic.is_descending_key = 1 THEN ' DESC' ELSE '' END
							FROM sys.indexes col_i
								INNER JOIN sys.index_columns col_ic ON col_i.object_id = col_ic.object_id AND col_i.index_id = col_ic.index_id
								INNER JOIN sys.columns col_c ON col_ic.object_id = col_c.object_id AND col_ic.column_id = col_c.column_id
							WHERE col_ic.is_included_column = 0
								AND col_i.object_id = i.object_id
								AND col_i.index_id = i.index_id
							ORDER BY col_ic.key_ordinal
							FOR XML PATH('')
							),1,2,'')
						)
					+ ')' + CHAR(13) + CHAR(10) +
					CASE WHEN (
							SELECT COUNT(1) 
							FROM sys.indexes col_i
								INNER JOIN sys.index_columns col_ic ON col_i.object_id = col_ic.object_id AND col_i.index_id = col_ic.index_id
								INNER JOIN sys.columns col_c ON col_ic.object_id = col_c.object_id AND col_ic.column_id = col_c.column_id
							WHERE col_ic.is_included_column = 1
								AND col_i.object_id = i.object_id
								AND col_i.index_id = i.index_id
							) > 0
					THEN
						' INCLUDE (' + 
						(SELECT STUFF((
							SELECT ', ' + QUOTENAME(col_c.name)
							FROM sys.indexes col_i
								INNER JOIN sys.index_columns col_ic ON col_i.object_id = col_ic.object_id AND col_i.index_id = col_ic.index_id
								INNER JOIN sys.columns col_c ON col_ic.object_id = col_c.object_id AND col_ic.column_id = col_c.column_id
							WHERE col_ic.is_included_column = 1
								AND col_i.object_id = i.object_id
								AND col_i.index_id = i.index_id
							ORDER BY col_ic.key_ordinal
							FOR XML PATH('')
							),1,2,'')
						) + ')'
					ELSE ''
					END +
					CASE WHEN i.has_filter = 1 THEN ' WHERE ' + i.filter_definition ELSE '' END +
					'    WITH (' + 
					CASE WHEN NOT(i.is_primary_key = 1 AND i.type = 2) THEN 'DROP_EXISTING = ON, ' ELSE '' END + 
					CASE WHEN i.fill_factor > 0 AND i.fill_factor < 100 THEN 'FILLFACTOR = ' + CONVERT(NVARCHAR(3), i.fill_factor) + ', ' ELSE '' END +
					'PAD_INDEX = ' + CASE WHEN i.is_padded = 1 THEN 'ON' ELSE 'OFF' END + ', ' +
					'IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END + ', ' +
					CASE WHEN NOT(i.is_primary_key = 1 AND i.type = 2) THEN 'ONLINE = ' + CASE WHEN @OnlineOption = 1 THEN 'ON' ELSE 'OFF' END + ', ' ELSE '' END +
					'ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END + ', ' +
					'ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END + ', ' +
					'DATA_COMPRESSION = ' + p.data_compression_desc +
					') ON ' +
					'[' + @NewFileGroupName + '];'
				
					+ CASE WHEN i.is_primary_key = 1 AND i.type = 2
					THEN '
		COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
	END CATCH'
				ELSE
					'
	'
					END
				FROM sys.indexes i
					INNER JOIN sys.objects o ON i.object_id = o.object_id
					INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
					INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
					INNER JOIN sys.partitions p ON o.object_id = p.object_id AND i.index_id = p.index_id
				WHERE o.type = 'U' /* USER_TABLE */
					AND i.type = @IndexType
					AND o.is_ms_shipped = 0
					AND ds.name = @OldFileGroupName
				ORDER BY o.name, i.name;
			END
			IF @cmd = ''
			BEGIN
				SET @msg = 'Warning: no indexes found on ' + @OldFileGroupName + '!';
				RAISERROR (@msg, 0, 1) WITH NOWAIT;
			END
			ELSE
			BEGIN
				RAISERROR (@cmd, 0, 1) WITH NOWAIT;
				EXEC sp_executesql @cmd;
			END
		END
		ELSE
		BEGIN
			SET @msg = 'ERROR: ' + @NewFileGroupName + ' does not exist!';
			RAISERROR (@msg, 0, 1) WITH NOWAIT;
		END
	END
	ELSE
	BEGIN
		SET @msg = 'ERROR: ' + @OldFileGroupName + ' does not exist!';
		RAISERROR (@msg, 0, 1) WITH NOWAIT;
	END
END
GO

This stored procedure can be called like this:

/* HEAPS */
EXEC dbo.MoveDataToCustomFilegroups @IndexType = 0
    , @OldFileGroupName = 'CurrentFileGroup'
    , @NewFileGroupName = 'NewFileGroup';

/* Clustered Indexes */
EXEC dbo.MoveDataToCustomFilegroups @IndexType = 1
    , @OldFileGroupName = 'CurrentFileGroup'
    , @NewFileGroupName = 'NewFileGroup';

/* Non-Clustered Indexes */
EXEC dbo.MoveDataToCustomFilegroups @IndexType = 2
    , @OldFileGroupName = 'CurrentFileGroup'
    , @NewFileGroupName = 'NewFileGroup';

/* XML Indexes */
EXEC dbo.MoveDataToCustomFilegroups @IndexType = 3
    , @OldFileGroupName = 'CurrentFileGroup'
    , @NewFileGroupName = 'NewFileGroup';

/* Spatial Indexes */
EXEC dbo.MoveDataToCustomFilegroups @IndexType = 4
    , @OldFileGroupName = 'CurrentFileGroup'
    , @NewFileGroupName = 'NewFileGroup';

Cleanup the stored procedure like this:

DROP PROCEDURE dbo.MoveDataToCustomFilegroups;

Once you’ve moved all data out of the filegroup, you can use the following commands to actually remove the filegroup and the files associated with that filegroup:

/*
    Get the filenames that below to the specified filegroup
*/
SELECT ds.name
	, df.name
	, df.physical_name
FROM sys.database_files df
	INNER JOIN sys.data_spaces ds ON df.data_space_id = df.data_space_id
WHERE ds.name = 'filegroup-name';

/*
    run this command once for each file in the filegroup
*/
ALTER DATABASE [database-name] REMOVE FILE [file-name];

/*
    run this command to remove the filegroup
*/
ALTER DATABASE [database-name] REMOVE FILEGROUP [filegroup-name];

As with all code, please test this in a non-production environment before using it in production.

Please note, by using this code, you assume all risks, and hold SQLServer.science, and the author of this post and code harmless.