Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-03-12 : 07:46:21
|
| Jonathan Hwang writes "We are unable to delete our empty filegroups!! This mysterious issue was the result of our filegroup consolidation. One of our SQL box has over 80 filegroups. For ease of management, we decided to consolidate these filegroups into 5 filegroups. We had a few choices of moving data between filegroups, but we choose to run a T-SQL script to re-assign cluster indexes on every tables as our target is to use that method to move the table to the target filegroup that we want. The process was done, we have moved all our data to those target files, but the end result is that we are ending up with lots of filegroups with 0 files and unable to delete! PROBLEM: Either through enterprise manager or T-SQL. We are unable to delete these filegroups!! It complains that we still have files in it!!When I run sp_helpfilegroup it returns filegroups as 0 files on those we wanted to delete. But when I run sp_helpfile, I see only those files that we wanted to keep. I also took the groupid from sp_helpfilegroup and try to match in sysindexes table. Some of them return matching results. So what is the problem here? why are we ending up with this mess?When we call Alter Database remove filegroup, Where does T-SQL make reference to before it removes filegroup? Why does it see it not empty? while sp_helpfilegoups say it's empty?Does any SQL GURU out there can hit the home run on this?thanksJon" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-03-12 : 07:51:21
|
| It's probably due to indexes or statistics that still reside on the old files. This is especially true of auto-created statistics, it's incredible how often they are hidden away. The same applies to text and image columns, if you have any in your tables.Take a look at the sysindexes table, it will give you the name and groupid of all indexes and statistics. Something like:SELECT OBJECT_NAME(id), name, filegroup_name(groupid), indid FROM sysindexesIndid will also tell you if something is a text/image column (=255). You can also use the INDEXPROPERTY function to determine which items are statistics. |
 |
|
|
|
|
|
|
|
Unable to delete filegroups!! Help!