[Last Call] Learn how to a build a cloud-first strategyRegister Now


Objects in Filegroups

Posted on 2007-08-05
Medium Priority
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
  • 2
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19636479
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
ID: 19636493
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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19636523
all UNIQUE and PRIMARY KEY constraints do that.

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

830 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