What are stubborn partitions in SQL Server and how do you delete them? This was an interesting issue I recently had to deal with on a client site that I thought our readers might find interesting.
The tables in use here are partitioned. The partition field is based upon a date field and we have a partition per month. There is a monthly maintenance job which creates our new partitions. The job should also delete the oldest partitions. This job has been failing to delete an old partition as the data file contained within is not empty. It’s stubborn!
If we try to remove this file we get the error “The File cannot be removed because it is not empty,” as you can see:
This is the file in question (I removed file names):
We can use the T-SQL query below to find all objects that believe they exist within our partition.
SELECT
OBJECT_NAME(p.OBJECT_ID) AS TableName
,p.partition_number AS PartitionNo
,FILEGROUP_NAME(a.data_space_id) AS FileGroup
,i.name AS IndexName
, *
FROM sys.allocation_units a
INNER JOIN sys.partitions p ON a.container_id = CASE WHEN a.type in(1,3) THEN p.hobt_id ELSE p.partition_id END AND p.OBJECT_ID > 1024
LEFT JOIN sys.indexes i ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
WHERE FILEGROUP_NAME(a.data_space_id) = 'FileGroupName'
ORDER BY FileGroup, TableName, p.partition_numberWhen run, you’ll get a list of all of the tables that believe they exist within this filegroup and how many data pages exist.
In our example, we have seven data pages that believe they exist in our partition. This is definitely a bug, because when we use our partition column and search our tables, we can see that no data exists.
SELECT * FROM TableName WHERE PartitionField BETWEEN ‘StartDate’ AND ‘EndDate’This is a bug in SQL Server and sensible human beings like us can’t resolve it. So, we’ll have to get creative.
Getting Creative: The Nuclear Option
From my experience, the only way to get rid of these rogue data pages is to truncate a table. By definition, truncating a table will deallocate all data pages associated with that table so we can get rid of these pesky hangers on. The big drawback is that you can’t put any sort of WHERE clause on a truncate, because you lose everything. It’s definitely the nuclear option when it comes to emptying out a table. We can’t do that to production tables in which we actually care about the data, obviously. We will have to perform a magic trick here to get this to work for us.
One of the great things about SQL Server partitioning is the ability to switch partitions between tables in the same filegroup. This consists of a metadata change only, rather than actually moving data.
Let’s combine both of these features to fix this pesky old filegroup.
From the first query above, we have our list of tables that are hanging around. For each of these we’re going to make a copy of the table structure (with a new name, something inventive like TableName_PartitioningTemp) along with all of the indexes that appear in our original table. We can then switch our partition over to the new table, truncate the new table, and then delete it (because we’re good children and tidy up after ourselves).
Our process will look like this:
- Create a copy of our table structure on the same partition
- Create all indexes required
- Switch our partition
- Truncate our new table
- Delete our new table
To create a copy of our table, we’ll just let SQL Server do the hard work by right clicking on our table and scripting it to a new query editor window.
One very important thing to note is that this will usually default to the PRIMARY filegroup. Be sure to change this to the correct filegroup:
We can then do the exact same and script out the necessary indexes. We’ll end up with a script looking something like this. REMEMBER, we’ll need to change the table name to something different than our live table.
This gives us our object ready to switch our partition into. Let’s do that by switching our partition to the new table. Make sure that your partition number is correct here.
Once this is done, we can then truncate our new _Partitioned table and delete it:
Our final script will look something like this;
CREATE TABLE [dbo].[Employees_Partitioned](
[EmployeeID] [INT] IDENTITY(1,1) NOT NULL,
[EmployeeName] [VARCHAR](100) NULL,
[EmployeeAddress] [VARCHAR](100) NULL,
[EmployeeDeptID] [INT] NULL
) ON [NewFileGroup]
GO
CREATE CLUSTERED COLUMNSTORE INDEX [IX_CS_Employee] ON [dbo].[Employees_Partitioned]
WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [NewFileGroup]
GO
ALTER TABLE [Employees] SWITCH PARTITION 1 TO [Employees_Partitioned]
TRUNCATE TABLE [Employees_Partitioned]
DROP TABLE [Employees_Partitioned]If you get any error messages, it may be that you’ve missed an index that’s required for the switch. Simply script it out and add it to your script.
Once you have completed this step, run the original script and you will remove this table from the list:
SELECT
OBJECT_NAME(p.OBJECT_ID) AS TableName
,p.partition_number AS PartitionNo
,FILEGROUP_NAME(a.data_space_id) AS FileGroup
,i.name AS IndexName
, *
FROM sys.allocation_units a
INNER JOIN sys.partitions p ON a.container_id = CASE WHEN a.type in(1,3) THEN p.hobt_id ELSE p.partition_id END AND p.OBJECT_ID > 1024
LEFT JOIN sys.indexes i ON i.OBJECT_ID = p.OBJECT_ID AND i.index_id = p.index_id
WHERE FILEGROUP_NAME(a.data_space_id) = 'FileGroupName'
ORDER BY FileGroup, TableName, p.partition_numberIf you have more than one table involved then you’ll have to do each one individually. Hopefully you don’t have too many, but this is definitely worth doing. Otherwise, you’ll never get rid of that filegroup.
Once the script runs and and the result of the script above is zero rows, you’re almost there. You need to check that the filegroup is not referenced by our partition scheme and function before we can finally delete it. We’d do this with a merge of the partition if it still exists.
ALTER PARTITION FUNCTION [FunctionName] () MERGE RANGE (N’2025-03-01’)
Then we can finally remove the file
ALTER DATABASE [DBName] REMOVE FILE [FileName]
That’s it! You’ve addressed stubborn partitions in SQL Server and removed what you don’t need.
