Multiple files in primary file group
-
I have a database with several filegroups which I am trying to merge as they no longer serve any recovery or performance purpose. The primary filegroup contains 2 files (totaling 42GB, one is only 16MB) and I would like to move the data from one to another. What is the best way of me doing this? Can you specify which file of a filegroup you want to recreate objects and their data on? The only way I can think of doing this so far is to duplicate the database with a single primary file and move all objects and data into the new database.
-
Use the DBCC SHRINKFILE ('filename', EMPTYFILE) command to remove all data from the file and then use the ALTER DATABASE command to remove the file.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
you won't be able to empty the primary file. (mdf extension)
Run sp_helpfile in the desire db to check wich is the primary file
-
I have tried DBCC SHRINKFILE ('filename', EMPTYFILE) whish reduced one off the files to 1MB and left the other at 42GB. I coudlnt drop the smaller file as it wasnt empty.
sp_helpfile says that there are 2 files in the PRIMARY file group.
-
I missed the bit about them being in the Primary group. Sorry. About the only thing you can do at this point is to create another database with the files/file groups how you want them and copy the data over. It's pain in the neck, and with 42Gb of data will take a while, but if you want to get rid of that file, that's the only way I know of.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
One suggestion might be to create another database with right filegroups and copy the data w/o index and put the index and rename the database. 50G database took me about < 2 hours to do all that.
It is simple and clean.
-
Er well it may be that your filegroups no longer have for you any purpose as far as backup is concerned they will however have a performance impact. SQL uses multiple threads to operate on filegroups - the difference this can make in a high volume environment can be significant. As a rule of thumb all multi gig tables should be on their own filegroup. There is a script on the database journal site that removes files from filegroups and puts each into its own filegroup. Generally the more concurrency you can get you should want from a performance standpoint. I would urge you to test this change out before you do it.
-
SQL Server can actually use multiple asynchronous I/O requests against a single file too. Now there may be some performance benefit in splitting the files across multiple I/O channels or spindles, but that is an I/O subsystem concern, not really a SQL Server issue per se.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Move Clustered index to the Filegroup of your choice and it moves your table. If you dont have a Clustered Index on the tables you need to move-then create them.
After that you can do the cleanup of your other filegroups.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply