Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2008 - File Group change Expand / Collapse
Author
Message
Posted Tuesday, May 7, 2013 8:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:54 AM
Points: 393, Visits: 973
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

Post #1450169
Posted Tuesday, May 7, 2013 8:31 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:33 PM
Points: 42,818, Visits: 35,943
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1450184
Posted Tuesday, May 7, 2013 8:31 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:21 AM
Points: 206, Visits: 764
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.
Post #1450186
Posted Tuesday, May 7, 2013 8:38 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:13 PM
Points: 33,198, Visits: 15,341
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)

http://www.mssqltips.com/sqlservertip/2601/using-multiple-filegroups-for-a-database-and-changing-the-default-filegroup/

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).







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1450192
Posted Tuesday, May 7, 2013 8:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:54 AM
Points: 393, Visits: 973
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?
Post #1450196
Posted Tuesday, May 7, 2013 8:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:33 PM
Points: 42,818, Visits: 35,943
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1450202
Posted Tuesday, May 7, 2013 9:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:54 AM
Points: 393, Visits: 973
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

Post #1450206
Posted Tuesday, May 7, 2013 11:44 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:21 AM
Points: 206, Visits: 764
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.
Post #1450405
Posted Wednesday, May 8, 2013 11:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:13 PM
Points: 33,198, Visits: 15,341
Note you still can't delete the primary file group.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1450718
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse