May 7, 2013 at 8:16 am
Changing Primary filegroup to a new filegroup
1. Any negative impact i should cover before making the above move. It wont affect existing objects in primary and new objects would be created in newfilegroup, unless we dont specfiy filegroup. Is that correct?
2. How to delete a filegroup
3. If the deleting filegroup is set as Primary, then how to delete it and what would be the premove steps
May 7, 2013 at 8:31 am
You can't make a different filegroup primary, the primary filegroup is the first one in the database and the one that contains the primary file. You can't delete the primary filegroup.
What you can do is set which filegroup is the default, any table or index that's created without specifying a filegroup then goes into the default filegroup
ALTER DATABASE <database name>
MODIFY FILEGROUP <filegroup name> DEFAULT;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2013 at 8:31 am
1. New objects will be created in the default filegroup if it's not specified in the create statement you can change the default filegroup if you want, the PRIMAY filegroup is the default filegroup by default (duh).
Existing user objects will not be moved, the only way to move an object from a filegroup to another is to drop and create it or reindex it to the new filegroup.
2. to remove a filegroup: ALTER DATABASE [MyDB] REMOVE FILEGROUP [MyFileGroup]
3. You can't remove the PRIMARY filegroup.
To delete a filegroup you have to remove all the files first.
To remove a file you need to empty the file.
You can't empty all the PRIMARY filegroup files (because of system objects), so you can't delete all the files, so you can't remove the PRIMARY filegroup.
On the other hand you can move (or recreate) all use tables to the new filegroup, shrink and lock the file size of the PRIMARY filegroup files, if your new filegroup is set as default then your PRIMARY filegroup will stay tiny and be barely used.
May 7, 2013 at 8:38 am
You can't change the primary filegroup AFAIK. You can change the default filegroup. (http://msdn.microsoft.com/en-us/library/aa933074%28v=sql.80%29.aspx)
If you are trying to move to a new disk, detach the database, move the files, attach it back.
Some people will move all user objects out of the primary filegroup for DR purposes.
To delete a filegroup, the ALTER DATABASE command can do this, but you have to move all other objects. Some can be dropped and created on the new filegroup. Fro tables, you need to move the clustered index (rebuild it on the new filegroup).
May 7, 2013 at 8:44 am
All,
I misquoted. I 'm gonna change default FG from Primary to new FG.
3. If the deleting filegroup is set as DEFAULT, then how to delete it and what would be the premove steps
4. if "SELECT * INTO" is used to create tables, it would alo be going to Default FG. Correct?
May 7, 2013 at 8:59 am
balasach82 (5/7/2013)
3. If the deleting filegroup is set as DEFAULT, then how to delete it and what would be the premove steps
Delete all the objects in that filegroup. Delete all the files. Set another filegroup as default.
4. if "SELECT * INTO" is used to create tables, it would alo be going to Default FG. Correct?
Any time that a filegroup is not specified, the object goes to the default filegroup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 7, 2013 at 9:08 am
1.to modify Default FG from primary to new FG, I would create nw FG, add new .ndf file, use ALTER DATABASE and make it default
2.to delete the new FG, make another FG as DEFAULT, would move all the objects in that FG to other FG, delete the .ndf file
If the tables hav no indexes*, then i should create tables in the New FG and move the data to the new table (in new FG) and delete OLD table in Old FG. Is this correct?
* Tables would be having clustered indexes, i want to know, in case threre are no indexes
May 7, 2013 at 11:44 pm
Either copy the data to a new table created in the other FG or create a clustered index on the new FG then drop it.
May 8, 2013 at 11:42 am
Note you still can't delete the primary file group.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply