August 21, 2012 at 9:01 am
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
August 21, 2012 at 9:06 am
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
August 21, 2012 at 9:25 am
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.
August 21, 2012 at 9:35 am
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
August 28, 2012 at 7:36 am
Hi Gila,
I have been trying to do this, but unsuccessful. Is there any example scripts you can share?
Thanks
August 28, 2012 at 8:07 am
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
August 29, 2012 at 3:24 pm
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'.
August 29, 2012 at 4:25 pm
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
August 30, 2012 at 6:52 am
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
August 30, 2012 at 8:33 am
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'.
August 30, 2012 at 11:03 am
Jysafe Lerroy (8/30/2012)
on the second step, when I drop the file that shrunk, wouldn't that delete all the data in that file?
No, read up on what ShrinkFile with the EMPTYFILE option does.
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
August 30, 2012 at 11:06 am
Sorry, CREATE CLUSTERED INDEX .... WITH DROP_EXISTING
Note that it will take time and use log space. How much depends on the size of the table.
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
August 31, 2012 at 7:44 am
Gila you are the best!!! It worked. Thank you so much!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply