| Author |
Topic |
|
leeholden
Starting Member
34 Posts |
 Posted - 2002-07-31 : 05:57:31
|
| I'm trying to remove a filegroup from my database, but it keeps telling me that it isn't empty. I can't however find anything else in the filegroup. I've even done a "SELECT * FROM SYSINDEXES WHERE GROUPID = 9" (9 is obviously the filegroup I want to remove - I checked it in SYSFILEGROUPS).Any suggestions about where I can find what's left?Cheers |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2002-07-31 : 07:15:25
|
| sp_helpfilegroup filegroupnameHTH |
 |
|
|
leeholden
Starting Member
34 Posts |
Posted - 2002-07-31 : 07:34:04
|
| Tried that.It doesn't tell me what objects are in the filegroup, it just tells me filenames, size, growth etc.Any other suggestions? |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2002-07-31 : 07:47:09
|
| a NASTY quick and dirty solution is:determine filegroups for DB - I take it you have that.use Sp_tables on db to get list of tables, or use sp_msforeachtable (undocumented sp - do a sp_help sp_msforeachtable against master to see usage) - and run a sp_help against each table. This will give you infgo on "Data_located_on_filegroup" which will list your filegroup.HTH |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-31 : 07:59:07
|
| This is a little cleaner:SELECT DISTINCT so.name, sfg.groupnameFROM sysobjects so INNER JOIN sysindexes si ON so.id=si.idINNER JOIN sysfilegroups sfg ON si.groupid=sfg.groupidWHERE sfg.groupname='PRIMARY'Modify the WHERE clause to find the filegroup you want, or eliminate to show all tables and their filegroups. |
|
|
|
leeholden
Starting Member
34 Posts |
Posted - 2002-07-31 : 08:01:51
|
| hardley seems QUICK to me, I've got over 500 tables!I think it may be a view. but again I have a fair few views. Any way I can get the file group of a view without doing them one at a time? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-31 : 08:13:31
|
| Views do not exist on filegroups, only the tables that are part of the view definition do.Edited by - robvolk on 07/31/2002 08:17:43 |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2002-07-31 : 08:16:08
|
| The term quick and dirty refers to developement - i.e it took 2 minutes to create.Agreed - 500 tables is excessive ( I don't even want to know what your ERD and DBD look like ). Try working with Rob's solution, views are defined in sysobjects. Do they have actual allocations, or are they built dynamically ? Looking at BOL I see they are virtual tables - thus they wouldn't exist.Edited by - wanderer on 07/31/2002 08:17:30 |
|
|
|
leeholden
Starting Member
34 Posts |
Posted - 2002-07-31 : 09:37:11
|
| robvolk,tried your query, it returned 0 rows. yet my alter database still won't remove the filegroup because it says it isn't empty.even tried an UPDATEUSAGE, but still no luck.I know that views arn't real tables, but it must store the sql somewhere; does it store it in a system table or something?Wanderer, ERD not that bad, as it is a Staging area server with several processes on it.We've got good naming conventions for our tables, but our filegroups have gone a bit out of control, that's why I'm trying to get rid of some of them. but they arn't playing! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-31 : 10:00:27
|
| The SQL for a view is stored in syscomments, but it doesn't have anything to do with filegroups (all of the system tables default to the primary filegroup anyway) There's no way a view can be holding a filegroup hostage.What's the exact error message and number that you're getting? I can't find anything in sysmessages like "Cannot drop filegroup xxx because it is not empty". |
|
|
|
leeholden
Starting Member
34 Posts |
Posted - 2002-07-31 : 10:04:03
|
| Server: Msg 5042, Level 16, State 7, Line 1The filegroup 'WORKING' cannot be removed because it is not empty. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-31 : 10:12:08
|
| The only thing I can think of is that an index, or a text/image column, was placed on that filegroup at some point. It *should've* shown up using the SQL I provided though.Try this: generate scripts for the entire database, all objects, and then search the .SQL file for "WORKING". If it shows up, you'll know which of the #!$@%! objects is causing the trouble. If NOT, well...I'm at a loss really. You may need to back up the entire database, recreate all of the objects from the script, and then restore the data. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-07-31 : 10:15:35
|
quote:
Tried that.It doesn't tell me what objects are in the filegroup, it just tells me filenames, size, growth etc.Any other suggestions?
Well, if you have files assigned to that filegroup, then the filegroup definitely ain't empty (the purpose of filegroup is to organize files -- not store data)! Remove the files, and then you should be able to kill the filegroup.Edited by - izaltsman on 07/31/2002 10:18:02 |
|
|
|
leeholden
Starting Member
34 Posts |
Posted - 2002-07-31 : 10:32:39
|
| Cheers iz,I was expecting the remove filegroup to tidy up all of the phisical files. Obviously not.cheers for all the suggestions rob |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-31 : 10:37:29
|
 (bangs head against desk until common sense returns...this could take a while)Ilya Zaltsman, thy name is "Shining Beacon of SQL Server Knowledge and Common Sense"  Edited by - robvolk on 07/31/2002 10:38:26 |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-07-31 : 10:42:20
|
Hmm... maybe I'll take that for my title!  |
|
|
|
|
What's in my filegroup?