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 |
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
 Posted - 2005-07-18 : 20:07:45
|
| If you have a DB with multiple files, I know you can merge them into a single file using "DBCC SHRINKFILE" to empty the file and then "ALTER DATABASE" to drop the file, as long as the files are in the same filegroup. However, how can you do this if they are in multiple filegroups? |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-07-18 : 20:29:53
|
quote:
Originally posted by nosepicker
If you have a DB with multiple files, I know you can merge them into a single file using "DBCC SHRINKFILE" to empty the file and then "ALTER DATABASE" to drop the file, as long as the files are in the same filegroup. However, how can you do this if they are in multiple filegroups?
you can't |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-07-18 : 20:35:00
|
| actually, you could probably just move the tables by rebuilding the table's clustered index and specify the new filegroup. THis would move the object form one filegroup to the other (this is probably what you did in the first place right?)I am not sure if you can drop the empty datafile once you are done though. I am not sure that an ALTER DATABASE REMOVE FILE command will remove a datafile that hasn't been emptied with a DBCC SHRINKFILE command, but it is worth a shot. Test this out and let us know if it works or not ;)-ec |
|
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-07-19 : 12:57:13
|
| Guess what ... it worked! I only had to move the user-defined indexes. I was able to empty and remove the file even though there were system-created indexes still existing in there (at least according to the system tables).Thank you very much, EC! A brilliant suggestion, I must say! This now saves me a lot of headache due to the previous DBA's weird setup. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-07-19 : 13:12:05
|
quote:
Originally posted by nosepicker
Guess what ... it worked! I only had to move the user-defined indexes. I was able to empty and remove the file even though there were system-created indexes still existing in there (at least according to the system tables).Thank you very much, EC! A brilliant suggestion, I must say! This now saves me a lot of headache due to the previous DBA's weird setup.
After I posted that suggestion I actually looked in BOL and saw that you could indeed drop an empty datafile with the ALTER DATABASE command. glad it worked out.-ec |
|
|
|
|
|
|
|
|
Merge multiple db files on multiple filegroups