Removing a filegroup that contains data.

2016-04-21 · by Hannah Vernon · in tools

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 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:     Hannah 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</code>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

Creates a temporary procedure to facilitate moving heaps, clustered indexes,

and non-clustered indexes from one filegroup to another.

CREATE PROCEDURE dbo.MoveDataToCustomFilegroups

(

@IndexType INT

, @OldFileGroupName SYSNAME

, @NewFileGroupName SYSNAME

)

BEGIN

Description: MOVE ALL INDEXES of specified @IndexType from @OldFileGroupName

to @NewFileGroupName

By: Hannah 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

ELSE

BEGIN

SET @msg = 'ERROR: ' + @NewFileGroupName + ' does not exist!';

RAISERROR (@msg, 0, 1) WITH NOWAIT;

END

ELSE

BEGIN

SET @msg = 'ERROR: ' + @OldFileGroupName + ' does not exist!';

RAISERROR (@msg, 0, 1) WITH NOWAIT;

END

GO</code>

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';

/* HEAPS */

EXEC dbo.MoveDataToCustomFilegroups @IndexType = 0

, @OldFileGroupName = 'CurrentFileGroup'