Changing FileGroup for File Name

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Changing FileGroup for File Name

13 posts, Page 1 of 2

1 2 »»

Changing FileGroup for File Name

Rate Topic

Display Mode

Topic Options

Author

Message

Jysafe Lerroy

Posted Tuesday, August 21, 2012 9:01 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 31, 2013 5:59 AM
Points: 15, Visits: 67

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

GilaMonster

Posted Tuesday, August 21, 2012 9:06 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

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

Jysafe Lerroy

Posted Tuesday, August 21, 2012 9:25 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 31, 2013 5:59 AM
Points: 15, Visits: 67

GilaMonster (8/21/2012)

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

GilaMonster

Posted Tuesday, August 21, 2012 9:35 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

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

Jysafe Lerroy

Posted Tuesday, August 28, 2012 7:36 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 31, 2013 5:59 AM
Points: 15, Visits: 67

Hi Gila,

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

Thanks

Post #1350926

GilaMonster

Posted Tuesday, August 28, 2012 8:07 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

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

Jysafe Lerroy

Posted Wednesday, August 29, 2012 3:23 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 31, 2013 5:59 AM
Points: 15, Visits: 67

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

GilaMonster

Posted Wednesday, August 29, 2012 4:25 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:18 PM
Points: 38,062, Visits: 30,359

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

Jysafe Lerroy

Posted Thursday, August 30, 2012 6:52 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 31, 2013 5:59 AM
Points: 15, Visits: 67

GilaMonster (8/29/2012)

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

Jysafe Lerroy

Posted Thursday, August 30, 2012 8:33 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 31, 2013 5:59 AM
Points: 15, Visits: 67

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 »

13 posts, Page 1 of 2

1 2 »»

Permissions