Monday, August 6, 2007

What to do when you want to drop a FileGroup

I was working on a system to partition tables on SQL Server 2005 which means I was creating database filegroups and database files and I needed to drop my newly created objects regularly to retest my code. But sometimes it was not possible to drop the filegroups with complaints that the filegroup was not empty. After a lot of googling I found that it is possible that statistics are defined on the filegroup (most likely automatic stats). To see this you can run the following query.

select object_name(id) AS TableName, * 
from dbo.sysindexes 
where groupid = object_id('<yourfilegroup>')
After dropping the statistics I could drop the filegroup without problems.

No comments: