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 12»»

Changing FileGroup for File Name Expand / Collapse
Author
Message
Posted Tuesday, August 21, 2012 9:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 9:44 PM
Points: 18, 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

Post #1347848
Posted Tuesday, August 21, 2012 9:06 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: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
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 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 #1347854
Posted Tuesday, August 21, 2012 9:25 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 9:44 PM
Points: 18, 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.
Post #1347871
Posted Tuesday, August 21, 2012 9:35 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: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
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 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 #1347881
Posted Tuesday, August 28, 2012 7:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 9:44 PM
Points: 18, Visits: 78
Hi Gila,

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

Thanks
Post #1350926
Posted Tuesday, August 28, 2012 8:07 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: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
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 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 #1350968
Posted Wednesday, August 29, 2012 3:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 9:44 PM
Points: 18, 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'.

Post #1351933
Posted Wednesday, August 29, 2012 4:25 PM


SSC-Forever

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

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 42,829, Visits: 35,961
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 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 #1351955
Posted Thursday, August 30, 2012 6:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 9:44 PM
Points: 18, 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
Post #1352167
Posted Thursday, August 30, 2012 8:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 9:44 PM
Points: 18, 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'.
Post #1352271
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse