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.