Thursday, November 5, 2009

Unable to remove Partition Filegroup

Today while removing a Filegroup we came across the famous error message.
The filegroup FileGroupNameXXX cannot be removed because it is not empty.

Normally, on getting this error message the first thing I do is try to shrink the Filegroup
DBCC SHRINKFILE (FileGroupNameXXX, 0)
This will clear all unused space from it.

However SQL Server informed me that the File does not exist. On further checking I noticed that the Filegroup does not have an associated File or Partition Scheme associated with it. Then why is it not empty and what is associated with it?

At this point, SQL Server DMV (Dynamic Management Views) came to my rescue and the following command showed there is a table along with an index still associated with the Filegroup.

SELECT ds.name, i.name, o.name
FROM sys.data_spaces ds
inner join sys.indexes i on i.data_space_id = ds.data_space_id
inner join sys.objects o on i.object_id = o.object_id
where ds.name = FileGroupNameXXX


This gave me the name of the Objects associated. So, I first dropped the table and ran the following command to remove the FileGroup.

ALTER DATABASE TestDB REMOVE FILEGROUP FileGroupNameXXX

Hope this helps...

No comments:

Post a Comment