How to remove a filegroup that was part of a SQL table partitioning function

In the following scenario an asker in the forums was having trouble dropping a filegroup that they believed was no longer in use however they were receiving the following error when attempting to drop the filegroup.

“Msg 5042, Level 16, State 7, Line 1
The filegroup ‘fg3′ cannot be removed because it is not empty.”

When they checked the filegroup they could see no indexed data in the filegroup therefore they were lost as to why this issue was occurring. If there is no data in a data file for a filegroup then you should be able to drop the data file and the file group.

However, if the filegroup was part of a table partitioning function then you need to alter the partition function before SQL will allow you to drop the file group even though it will allow you to drop the data file in the filegroup if the data file is empty. If you try to drop the data file and file group when the data file is empty however the file group is part of a partition function you receive the following error.

The file ‘data3′ has been removed.
Msg 5042, Level 16, State 12, Line 1
The filegroup ‘fg3′ cannot be removed because it is not empty.

The first problem they had was they could not be certain the data file contained no data as they were simply checking that there was no index data in the filegroup via the following script.

SELECT groupid, name
FROM dbo.sysindexes WHERE groupid = 4
ORDER BY groupid, name
groupid name
------- --------------------------------------------------------------------------------------------------------------------------------

(0 row(s) affected)

A better method when partitioning is in use is to return the partition number information for the partitioned table via the following code documented here.

/* Where the partition function is named TestPF and one of the columns is Email */

select CustomerID, Email, $partition.TestPF(Email) Partition

FROM dbo.CustomerEmail;

GO

 

CustomerID  Email                          Partition
----------- ------------------------------ -----------
1           andrew@adventure-works.com     1
2           garth@adventure-works.com      2
3           sharon@adventure-works.com     3

(3 row(s) affected)

As you can see the above result shows there is data in the filegroup it’s just not indexed (done here on purpose you may have heaps in your database so you should check before you start dropping objects!).

Now let’s actually walk through the scenario by creating a test database with some filegroups and a partitioned table.

First let’s create the database, filegroups and data files.

CREATE DATABASE MergeDropFG;

GO

USE MergeDropFG;

GO

-- Add the FGs

ALTER DATABASE MergeDropFG ADD FILEGROUP fg1;

ALTER DATABASE MergeDropFG ADD FILEGROUP fg2;

ALTER DATABASE MergeDropFG ADD FILEGROUP fg3;

GO

-- Create the datafiles on the filegroups

ALTER DATABASE MergeDropFG ADD FILE ( NAME = data1, FILENAME = 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2A\MSSQL\DATA\MergeDropFGFG1.ndf',

SIZE = 1MB, MAXSIZE = 100MB, FILEGROWTH = 1MB) TO FILEGROUP fg1;

ALTER DATABASE MergeDropFG ADD FILE ( NAME = data2, FILENAME = 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2A\MSSQL\DATA\MergeDropFGFG2.ndf',

SIZE = 1MB, MAXSIZE = 100MB, FILEGROWTH = 1MB) TO FILEGROUP fg2;

ALTER DATABASE MergeDropFG ADD FILE ( NAME = data3, FILENAME = 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2K8R2A\MSSQL\DATA\MergeDropFGFG3.ndf',

SIZE = 1MB, MAXSIZE = 100MB, FILEGROWTH = 1MB) TO FILEGROUP fg3;

GO

Now let’s create the partition function, scheme and table.

-- Create partition function

CREATE PARTITION FUNCTION TestPF (nvarchar(30))

AS RANGE RIGHT FOR VALUES ('G', 'N');

GO

-- Create partition scheme

CREATE PARTITION SCHEME TestPS AS PARTITION TestPF TO (fg1, fg2, fg3);

GO

-- Create a partitioned table

CREATE TABLE dbo.CustomerEmail ( CustomerID int, Email nvarchar(30) ) ON TestPS (Email);

GO

Now let’s insert some data and check if we can see it.

-- Insert some data

INSERT dbo.CustomerEmail VALUES (1, 'andrew@adventure-works.com'),

(2, 'garth@adventure-works.com'),

(3, 'sharon@adventure-works.com');

GO

-- Use your code to show there is no data

SELECT groupid, name FROM dbo.sysindexes WHERE groupid = 4 ORDER BY groupid, name

We can’t see the data as I explained above as it is a heap. I know I would not normally use a heap for a partitioned table but I highlight this as you want to be sure of what data might be in the filegroup.

(3 row(s) affected)
groupid name
------- --------------------------------------------------------------------------------------------------------------------------------

(0 row(s) affected)

Now let’s look for the data a slightly better way.

select CustomerID, Email, $partition.TestPF(Email) Partition

FROM dbo.CustomerEmail; GO

 

CustomerID  Email                          Partition
----------- ------------------------------ -----------
1           andrew@adventure-works.com     1
2           garth@adventure-works.com      2
3           sharon@adventure-works.com     3

(3 row(s) affected)

Ok you can see above we have some data in partition 3 which is on FG3. As you would expect we cannot drop the data file or filegroup.

USE [MergeDropFG]
GO ALTER DATABASE [MergeDropFG]  REMOVE FILE [data3]
GO ALTER DATABASE [MergeDropFG] REMOVE FILEGROUP [fg3]
GO

 

Msg 5042, Level 16, State 1, Line 1
The file 'data3' cannot be removed because it is not empty.
Msg 5042, Level 16, State 7, Line 1
The filegroup 'fg3' cannot be removed because it is not empty.

If the data file was empty for FG3 then you would see a success on the data file drop but a failure on the drop for the filegroup.

The file 'data3' has been removed.
Msg 5042, Level 16, State 12, Line 1
The filegroup 'fg3' cannot be removed because it is not empty.

Ok, now we need to get the data from partition 3 into partition 2 so we can clear the FG3 that is used in partition 3. This is where we need to alter the function and merge the ranges.

-- Alter the partition function to merge the range from partition 3 into partiton 2

ALTER PARTITION FUNCTION TestPF() MERGE RANGE ('N')

Now when we select the data we see that the data from partition 3 still exists but it was merged into partition 2.

SELECT Email, $partition.TestPF(Email) Partition

FROM dbo.CustomerEmail;

GO

 

Email                          Partition
------------------------------ -----------
andrew@adventure-works.com     1
garth@adventure-works.com      2
sharon@adventure-works.com     2

(3 row(s) affected)

We can now finally drop the file group FG3 and the data file.

USE [MergeDropFG]
GO ALTER DATABASE [MergeDropFG]  REMOVE FILE [data3]
GO ALTER DATABASE [MergeDropFG] REMOVE FILEGROUP [fg3]
GO

 

The file 'data3' has been removed.
The filegroup 'fg3' has been removed.

I would like to thank Greg Low as I borrowed some of his code to create the partitioned table.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>