SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Changing FileGroup for File Name


Changing FileGroup for File Name

Author
Message
Jysafe Lerroy
Jysafe Lerroy
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 78
Hi,

How can I change a file gorup for a specific file name?

The issue that I'm having is that I already have a live/production database and I want to chagne one of the file names to a different filegroup.

For example:
Logical Name              FileGroup                   File Name
DB DB DB.MDF
DB1 DB1 DB_1.NDF
DB2 DB2 DB_2.NDF
DB3 DB3 DB_3.NDF
DB4 DB3 DB_4.NDF



Change File Name or Logical Name DB4 from DB3 to DB4:
Logical Name              FileGroup                   File Name
DB DB DB.MDF
DB1 DB1 DB_1.NDF
DB2 DB2 DB_2.NDF
DB3 DB3 DB_3.NDF
DB4 DB4 DB_4.NDF


GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220137 Visits: 46279
No. You'll have to create a new file in a new filegroup, then shrink the one you want to remove with the EMPTYFILE option and then drop it. The move the objects that you want in FG4 into that fielgroup.

Files can't move between filegroups because objects are assigned to filegroups and stored in files, if you moved one of the files from FG3 to FG4 you'd suddenly have objects spanning filegroups, which can't happen

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


Jysafe Lerroy
Jysafe Lerroy
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 78
GilaMonster (8/21/2012)
No. You'll have to create a new file in a new filegroup, then shrink the one you want to remove with the EMPTYFILE option and then drop it. The move the objects that you want in FG4 into that fielgroup.

Files can't move between filegroups because objects are assigned to filegroups and stored in files, if you moved one of the files from FG3 to FG4 you'd suddenly have objects spanning filegroups, which can't happen


Hi Gila thanks for getting back to me.

I got the EMPTYFILE option, but how do I move the object to FG4? Because when I drop it, the file name is deleted. I must be missing something.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220137 Visits: 46279
You rebuild the clustered indexes onto the new filegroup. You have 2 files in FG3. When you do a shrink with EmptyFile on one of them, the objects will be entirely in the other file on FG3. You then move them to FG4 as you would move any object to a new filegroup.

You cannot move a file between filegroups. You have to delete the file, then create a new one in the new filegroup and then move the tables over that you want on the new 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


Jysafe Lerroy
Jysafe Lerroy
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 78
Hi Gila,

I have been trying to do this, but unsuccessful. Is there any example scripts you can share?

Thanks
GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220137 Visits: 46279
The seperate steps are in Books Online. What exactly are you having trouble with and what are the commands you're running?

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


Jysafe Lerroy
Jysafe Lerroy
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 78
I tried the script below, but I get the error message below.

ALTER DATABASE TestDB
ADD FILEGROUP FG4;

ALTER DATABASE TestDB
ADD FILE
(
NAME = FG4,
FILENAME = 'C:\TestDB\dbfile4.ndf',
SIZE = 2000,
FILEGROWTH = 10%
)
TO FILEGROUP FG4




Msg 5035, Level 16, State 1, Line 1
Filegroup 'FG4' already exists in this database. Specify a different name or remove the conflicting filegroup if it is empty.
Msg 1834, Level 16, State 1, Line 4
The file 'C:\TestDB\dbfile4.ndf' cannot be overwritten. It is being used by database 'TestDB'.

GilaMonster
GilaMonster
SSC Guru
SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)SSC Guru (220K reputation)

Group: General Forum Members
Points: 220137 Visits: 46279
You're not trying to add a filegroup (FG4 already exists).
You're not trying to add an existing file to that new filegroup. As I already said, you cannot move files between filegroups in any way at all.

The steps are:

Shrink the file that you want to remove (Not move. Remove) with the EmptyFile option
Drop the file you have just shrunk
Add a new file to the existing filegroup FG4
Move any objects you want in the new filegroup to the new filegroup via CREATE CLUSTERED INDEX

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


Jysafe Lerroy
Jysafe Lerroy
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 78
GilaMonster (8/29/2012)
You're not trying to add a filegroup (FG4 already exists).
You're not trying to add an existing file to that new filegroup. As I already said, you cannot move files between filegroups in any way at all.

The steps are:

Shrink the file that you want to remove (Not move. Remove) with the EmptyFile option
Drop the file you have just shrunk
Add a new file to the existing filegroup FG4
Move any objects you want in the new filegroup to the new filegroup via CREATE CLUSTERED INDEX


Hi, thanks for getting back to me on this.

on the second step, when I drop the file that shrunk, wouldn't that delete all the data in that file?
on the third step, if the data is deleted that is in the file, how would i move the data?

Sorry but I'm a bit confused here.

Thanks for your patience with me on this
Jysafe Lerroy
Jysafe Lerroy
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 78
By the way I went thourh the steps and i'm getting the error message below when I run the scrip below

 CREATE CLUSTERED INDEX X1_ACTION ON ACTION (PERSONID, ACTIONSTATUSDT, ACTIONSTATUSID, POLICYID, TRANSTATUSID) ON [TKCS9];



The operation failed because an index or statistics with name 'X1_ACTION' already exists on table 'ACTION'.

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