Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2008 - File Group change


SQL 2008 - File Group change

Author
Message
balasach82
balasach82
SSC-Addicted
SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)

Group: General Forum Members
Points: 459 Visits: 1079
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47204 Visits: 44367
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

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


Oliiii
Oliiii
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 777
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.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36076 Visits: 18737
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
My Blog: www.voiceofthedba.com
balasach82
balasach82
SSC-Addicted
SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)

Group: General Forum Members
Points: 459 Visits: 1079
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?
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47204 Visits: 44367
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

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


balasach82
balasach82
SSC-Addicted
SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)SSC-Addicted (459 reputation)

Group: General Forum Members
Points: 459 Visits: 1079
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
Oliiii
Oliiii
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 777
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.
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36076 Visits: 18737
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
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search