August 8, 2006 at 9:22 am
Hi All,
Has anyone got a stored procedure that moves tables onto a different filegroup, all I have seen is moving indexes. I asked for a stored procedure, as i will be moving loads of tables, some with clustered indexes, some without.
Although, I have a script that moves data to different filegroups, this script wasnt in a stored procedure, but as I converted it into a stored procedure, if i run it, it keeps locking the checkpoint process on the database.
Can anyone help ?
Thanks in advance.
J.B
August 8, 2006 at 9:42 am
John
For tables with a clustered index, drop the clustered index and recreate it on the new filegroup. For tables without a clustered index, create one on the new filegroup. Then you can drop it afterwards if you've got a good reason for not having one. Probably best to drop all your non-clustered indexes first and recreate them at the end as well, otherwise all this may run a bit slow.
I don't know about putting this in a stored procedure, but it should be fairly easy to write something that generates a script to do all the dropping and recreating of clustered indexes for you.
Alternatively you can do it one table at a time in Enterprise Manager. Right-click on the table, choose Design Table, right-click on any row, choose Properties and change the filegroup(s) as required.
John
August 9, 2006 at 4:06 pm
Is it in the EM by moving Clustered Index to another Filegroup you can also move table ( which has that Clustered index?!)
August 10, 2006 at 1:30 am
No, you do this in Query Analyzer. You will find the syntax in Books Online under ALTER TABLE.
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply