Solving "The filegroup ” cannot be removed because it is not empty" error

This is a follow up post to my earlier post How to remove a filegroup that was part of a SQL table partitioning function. In this post I will explain another scenario which prevents you from dropping a existing filegroup that is locked due to a partition scheme.

First off we have a test database with 3 file groups and one partition scheme and function with a single range.

-- Create a Test  database for testing
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

-- Create partition function 
CREATE PARTITION FUNCTION TestPF (nvarchar(30))
AS RANGE RIGHT FOR VALUES ('G');
GO


-- Create partition scheme
CREATE PARTITION SCHEME TestPS
AS PARTITION TestPF TO (fg1, fg2);
GO

-- Create a partitioned table
CREATE TABLE dbo.CustomerEmail 
( CustomerID int, 
  Email nvarchar(30)
)
ON TestPS (Email);
GO

-- Insert some data
INSERT dbo.CustomerEmail
  VALUES (1, 'andrew@adventure-works.com'),
         (2, 'garth@adventure-works.com'),
         (3, 'sharon@adventure-works.com');
go

When we script out the partition scheme via SSMS we can see that the scheme does not use FG3.

USE [MergeDropFG]
GO /****** Object: PartitionScheme [TestPS] Script Date: 04/30/2012 23:06:53 ******/ CREATE PARTITION SCHEME [TestPS] AS PARTITION [TestPF] 

TO ([fg1], [fg2]) GO 

Now we want to confirm there is no data in the FG3.

SELECT Email, $partition.TestPF(Email) Partition FROM dbo.CustomerEmail; GO
Email                          Partition
------------------------------ -----------
andrew@adventure-works.com     1
andrew@adventure-works.com     1
garth@adventure-works.com      2
sharon@adventure-works.com     2
garth@adventure-works.com      2
sharon@adventure-works.com     2

Ok. So we should be able to drop the FG3 filgroup and database right?

USE [MergeDropFG]
GO ALTER DATABASE [MergeDropFG]  REMOVE FILE [data3]
GO ALTER DATABASE [MergeDropFG] REMOVE FILEGROUP [fg3]
GO
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. So we could drop the data file which confirms that there was no data in the data file. However we could not drop the file group as SQL thinks it is not empty. We know above that the partition scheme did not specify FG3. So what is going on?

If we execute the following query to check the system tables we can see that SQL does have the FG3 in the partition scheme.

select partition_scheme_id,destination_id,a.data_space_id,name,b.data_space_id
from sys.destination_data_spaces a right join sys.filegroups b
on a.data_space_id = b.data_space_id
partition_scheme_id destination_id data_space_id name                                                                                                                             data_space_id
------------------- -------------- ------------- -------------------------------------------------------------------------------------------------------------------------------- -------------
NULL                NULL           NULL          PRIMARY                                                                                                                          1
65601               1              2             fg1                                                                                                                              2
65601               2              3             fg2                                                                                                                              3
65601               3              4             fg3                                                                                                                              4

Now I want to find out which partition function and scheme FG3 is being used by.

select f.name as [Function Name], f.type_desc, s.name as [Scheme Name], s.type_desc
from sys.partition_schemes s join sys.partition_functions f
on s.function_id = f.function_id
where s.data_space_id = '65601'
Function Name                                                                                                                    type_desc                                                    Scheme Name                                                                                                                      type_desc
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------
TestPF

We can see above that the TestPS partition scheme is using FG3 however we know that the partition scheme DDL did not reference FG3, so how do we clean this up. Obviously since the scheme did not use FG3 in the definition we cannot complete a MERGE operation.

In this instance we need to use the NEXT USED option in ALTER PARTITION.

ALTER PARTITION SCHEME TestPS NEXT USED

Now if we re-check the FG status we can see that FG3 is no longer being used by the partition scheme.

select partition_scheme_id,destination_id,a.data_space_id,name,b.data_space_id
from sys.destination_data_spaces a right join sys.filegroups b
on a.data_space_id = b.data_space_id
partition_scheme_id destination_id data_space_id name                                                                                                                             data_space_id
------------------- -------------- ------------- -------------------------------------------------------------------------------------------------------------------------------- -------------
NULL                NULL           NULL          PRIMARY                                                                                                                          1
65601               1              2             fg1                                                                                                                              2
65601               2              3             fg2                                                                                                                              3
NULL                NULL           NULL          fg3                                                                                                                              4

This worked as at some point the ALTER PARTITION statement was executed with the FG3 file group name.

ALTER PARTITION SCHEME TestPS NEXT USED fg3

When a file group name is specified this marks the file group specified as the next file group to be used in the scheme. Therefore even though the partition scheme was created without specifying FG3 the alter partition scheme has marked FG3 as part of the scheme. When you execute the statement without specifying the file group name when a file group with the NEXT USED property exists that file group loses its next used state and therefore removes the lock on the file group.

Therefore now we can drop the FG3 file group.

USE [MergeDropFG]
GO ALTER DATABASE [MergeDropFG] REMOVE FILEGROUP [fg3]
GO
The filegroup 'fg3' has been removed.

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>