How do I move existing tables and indexes to new filegroup

[ptheriault]

I'm trying to move a table to a new filegroup.
I have been following the instructions at the following link but I get an error.

http://msdn2.microsoft.com/en-US/library/ms175905.aspx

Recreate failed for index PK...
An exception occured while executing batch
table ... already has PK.
SQL Error 1779

Has anyone else had this problem. Am I doing it wrong?


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

 

[Catadmin]

Your problem may be that you're moving a Primary Key which is, by default, a clustered index. If you look at the top of the Storage tab your article references, it says:

You can allow online processing of DML statements while moving a clustered index to another filegroup or partition scheme

Check BOL with the keywords "index moving [SQL Server]" and see if that helps you out any.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

I did that. There is a check box to allow online processing. There is also a check box to Drop existing. It is grayed out but selected.

By the way. How do you add a quote to a thread?

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

 

[Catadmin]

Click on Process TGML at the bottom of the Submit Post screen. It'll give you a list of the little HTML-like tags you can use to set apart stuff for quotes, code, etc.

Try dropping the Primary Key and recreating it on the new filegroup.

Does the new filegroup have any files in it?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Currently the new filegroup is empty. This is just in a test db that I created. I have to figure out how best to move a prod db. I know I could use the alter table by rename method but that would be a big pain in the butt considering it's over 300 tables.


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

 

[Catadmin]

If you don't have a file associated with the filegroup, that might be your problem.

Have you resolved this issue yet?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

I've had to put this aside while we try to stabalize our Old Oracle environment. Hopefully I'll be able to get back to it next week.
Also I do have a file accociated with the filegroup. I've been able to create new tables, and if I drop and recreate the PK it will move it. But it won't work through SSMS.

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

 

[Catadmin]

That is very odd. I wonder if it's because it's a clustered index... Or if this is another hidden bug that MS forgot to test out.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Have you tried it? I plan on dedicating a lot of time to it on Monday. Are you on the east coast?

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

 

[Catadmin]

Depends on if you consider Florida to be east coast or west coast since it has both. @=) East Coast & east coast of FL. How's that for an answer? @=)

I started to play with it when you started this thread, then was given a priority project at work so wasn't able to continue. If I get a chance later, I'll see if I can get it to work myself.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Thanks,
FL would be nice right about now. I'm up in little Rhode Island.

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

 

[Catadmin]

AHA! I went re-reading the article you listed above. At the very top, it says:

If a table has a clustered index, moving the clustered index to a new filegroup moves the table to that filegroup.

I'm thinking you have to move the entire table, rather than the clustered index. Also, there might be an issue with the fact that a PK is a constraint as well as an Index, so that might be the problem.

BTW, I also tried this method with a UNIQUE Index and it does not work either. When I tried a T-SQL DROP Index..With (Move TO...), I got the following errors:

Msg 3723, Level 16, State 4, Line 1
An explicit DROP INDEX is not allowed on index 'dbo.<tablename>.<indexName>'. It is being used for PRIMARY KEY constraint enforcement.

and

Msg 3723, Level 16, State 5, Line 1
An explicit DROP INDEX is not allowed on index 'dbo.<tablename>.<indexName>'. It is being used for UNIQUE KEY constraint enforcement.

Have you tried this method with a non-clustered, non-unique constraint index?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

I'll try it with a non-clustered to see what happens.

I'm thinking you have to move the entire table, rather than the clustered index. Also, there might be an issue with the fact that a PK is a constraint as well as an Index, so that might be the problem.

I thought that was how you moved a table. The data is actually stored in the leaf level of the Clustered Index. Maybe the problem is because of the constraint.

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

 

[ptheriault]

If I drop the PK, I can then run a create UNIQUE CLUSTERED INDEX statement on the table for the filegroup I want. It will then move the table to that file group. I confirmed that by selecting properties for the table and in the Storage section it had the new filegroup name. I'll just have to do that 215 times for each table in my database!


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

 

[Catadmin]

Script the drops with the GENERATE SQL SCRIPT thing. Then all you have to worry about is your CREATE statements.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"