Objects in Filegroups
Posted on 2007-08-05
I am trying to remove some excess filegroups.
I have moved all the indexes into the primary filegroup, and have been able to delete the data files, but can't delete the filegroups themselves.
The error message from Enterprise Manager is:
Error 20540: [SQL-DMP]Can not drop file gropu when there are objects in the group.
The errore message from Query Analyser is:
Server: Msg 5042, Level 16, State 8, Line 1
The filegroup 'secondary' cannot be removed because it is not empty.
I found this query on the web
left( o.[name], 20 ) TableName
FROM sysindexes i
right outer JOIN sysfilegroups f
ON i.groupID = f.groupID
left outer JOIN sysobjects o
ON i.ID = o.ID
WHERE f.GroupID in ( 2, 3 ) --* New FileGroup*
I get a whole bunch of indexes with names like _WA_Sys_Active_607D3EDD that wont drop.
Suggestions anyone ...
Thanks in advance,