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 |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2002-10-29 : 18:37:57
|
| Argh! I'm experimenting with filegroups, as you'll probably know from some of my other posts.I just mistakenly assigned a new file to the primary filegroup. I realized my mistake immediately, but already couldn't delete the file ("Is not empty"). I can't seem to reassign it. Is there anything I can do to convince SQL server to 1) not put any more data in that file, 2) take the existing data out of that file, and 3) delete the file?Thanks-b |
|
|
DavidD
Yak Posting Veteran
73 Posts |
Posted - 2002-10-30 : 00:15:41
|
| re-asssign the tables in that file group to another file group (design table in EM -> table) and then delete the filegroup. |
 |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-10-30 : 18:56:16
|
| Already done, with no lock. I also did the "dbcc shrinkfile(filename,EMPTYFILE)" thing, with no luck.DBCC SHRINKFILE reports that there are 8 pages in the file... but I still can't get rid of the thing.Any ideas?-b |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-10-30 : 19:49:38
|
| How about creating a CLUSTERED INDEX on the desired filegroup. That should move the actual data to the filegroup you want. |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-10-31 : 14:52:39
|
| The file I want to remove is one of two files in the primary filegroup, so in theory DBCC SHRINKFILE(filename,EMPTYFILE) should just move all of its data into the other file in the filegroup, thus allowing me to ALTER DATABASE REMOVE FILE. No luck. I've tried everything; backing up the DB, you name it. Worse, EMPTYFILE doesn't seem to be doing its job. Books online says "Migrates all data from the specified file to other files in the same filegroup. Microsoft® SQL Server™ no longer allows data to be placed on the file used with the EMPTYFILE option. This option allows the file to be dropped using the ALTER DATABASE statement."It *does* seem to be moving all of the data -- except 8 pages -- but it does *not* seem to be keeping SQL server from placing data in that file. If I do two DBCC SHRINKFILE (filename,EMPTYFILE) commands in a row, the second one is almost instantaneous (presumably because there's no data in the file). If I wait a few hours and then do it again, it takes a while, which I'm interpreting to mean that data is ending up back in the file.I'm at a loss. Anyone?Thanks-b |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-10-31 : 17:44:10
|
 D'oh! Sorry Aiken, I was thinking tables not files... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-31 : 17:50:19
|
| How about indexes? Are there any non-clustered indexes on any tables that were part of that filegroup? Putting the clustered index on a new file wouldn't move the non-clustered indexes off. Also check if there are any statistics that were created for that table. Also, what about text/ntext/image columns? |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-11-01 : 13:14:49
|
| No indexes, no columns. Statistics is interesting, though -- is there any way to check what statistics exist in a file, and/or delete them?Thanks-b |
|
|
|
|
|
|
|
|
Argh! Removing file from filegroup?