Moving filegroups 2

[mutley1]

Hi all,

I have a database with several filegroups and there is 1 on a drive on it's own that is running out of space. I have another drive with ample space - can someone point me in the direction of docs or tell me the best way to move that filegroup cleanly from disk A to disk B?

Cheers,

M.

 

[philhege]

I think the right thing to do is to detach the database, move the files, then re-attach the DB and specify the new location of the files.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[mrdenny]

You have several options.

1. Don't move the file. Add a new file to the file group where the new file is on the new disk. This is a zero down time option.

2. Move the data. Create a new file in the same filegroup on the new disk. Use the DBCC SHRINKFILE command with the EMPTYFILE switch to tell SQL to move all the data from the old file to the new file. (This will work will all files in all file groups except for the first file in the PRIMARY file group.) This is a zero down time option, however users may see performance issues and blocking while the data is being moved.

3. Move the file by detaching the database. Detach the database by using either the GUI or the sp_detach_db procedure and then move the file, then reattach the database using either the GUI or the sp_attch_db command. This option requires downtime.

While number 3 is the easiest to do, it requires the most outage to complete. Any of the three will work just fine.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mutley1]

Thanks Denny,

The problem is we need to keep the file name the same (apparently the 3rd party supplier software is tetchy about names etc.)

Say the current filegroup is called FG1 and contains a file FILE1 on the D drive. E drive has plenty of space.

Would it be feasible to:
1. Create FILE1_NEW in FG1 on the E drive.
2. Use the DBCC SHRINKFILE command with the EMPTYFILE command as per your suggestion number 2 to transfer from FILE1 to FILE1_NEW
3. Delete FILE1 on D
4. Create FILE1_NEW on E
5. EMPTYFILE again from FILE1_NEW to the "moved" FILE1

Unfortunately we have log shipping as well so I assume the drives will have to be mirrored exactly so it will have yo be done on the standby server?

Thanks,

M

 

[mutley1]

P.S. The file is about 11GB - guess that will take a bit of time? Someone has tested a copying data with a select into in batches of 250 in a while loop and that was 1 and a half hrs.

 

[ptheriault]

Mutley,
Using option 1 as suggest by Denny is very easy.
Just right click on the database and select properties,
Then select files and click on the add button to add a new file. Place the new file on the new drive. Make sure you select the FG1 as the file group. Then set the old file to stop autogrowing.
You won't need to change anything else. SQL Server will start using the new file immediately

- Paul
- Database performance looks fine, it must be the Network!

 

[mutley1]

Thanks Paul,

Problem is the file name has to be the same and they have specified all the data needs to be in the 1 place. Don't suppose there is any way round that is there?

Sorry if I'm being thick, but I haven't worked much with filegroups. from what i understand, you have a filegroup, assign files to it, then tables / indexes etc..... If I create a new file, I am going to have to shift the data otherwise the stored proc will write to the old table? I'm dazed and confused......apologies.

M.

 

[ptheriault]

When you say 'They' do you mean the vendor? When you add a new file you don't need to modify anything else. All the data is still in the same database (.mdf) file. Even if you added a new file and file group, moved 1/2 of the tables in your database to the new file group SQL Server will still know where the data is and the application would not need to be changed.
There are some great things with using multiple files and file groups. If configured properly you can spread I/O across multiple arrays increasing performance. But if the vendor won't support your changes to the database then there is nothing you can do. Is it possible to schedule some down time for the db?
If you can then the best option left to you is to detach, copy the .mdf file to the new drive and re-attach.
I would disable the log shipping jobs while you are doing this. You won't need to make any changes to log shipping once you re-attach the database.

- Paul
- Database performance looks fine, it must be the Network!

 

[mutley1]

Thanks Paul,

Much appreciated.

 

[ptheriault]

no problem, good luck.

- Paul
- Database performance looks fine, it must be the Network!