Objects in Filegroups

Posted on 2007-08-05
Last Modified: 2013-11-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
      , o.[type]
      , i.[name]
      , i.[ID]
      , f.[groupname]
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*
order by
      , o.[name]
      , i.[name]
I get a whole bunch of indexes with names like _WA_Sys_Active_607D3EDD that wont drop.

Suggestions anyone ...

Thanks in advance,
Question by:David Todd
    LVL 142

    Accepted Solution

    Those indexes are generated by the primary key constraints for the tables.
    you will have to get those indexes moved also, which mainly means to drop and recreated the corresponding constraint
    LVL 35

    Author Comment

    by:David Todd
    Hi AngelIII,

    Thanks for that.

    Due to replication more than likely I can't do that live, but will try on a test system.

    There is more than one per table. Is this for other constraints as well?

    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    all UNIQUE and PRIMARY KEY constraints do that.

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now