June 18, 2008 at 4:41 am
Hi,
I am trying to move some large tables to a new FileGroup I have created.
The operations complete successfully and I can see that the clustered index has been moved to the Secondary filegroup, however there has not been a significant increase in the available amount of space in the primary filegroup. This can be seen by selecting the shrink data file option in SSMS, which provides an indication of the freespace remaining in a data file. The secondary Filegroup also states that is has 99% available space, given that it is 20GB in size and should hold 15GB it would seem the operation has not moved the physical table data.
The fact that the process only took a couple of seconds to move to a different SAN some 15GB of data also seems very unlikely.
I was under the impression that the table data had to reside on the same filegroup as the clustered index?
Below is the code for reference.
ALTER TABLE dbo.TableName DROP CONSTRAINT [TableName_PK]
WITH (MOVE TO SECONDARY)
GO
--Now rebuild the constraint on the relocated table
ALTER TABLE dbo.TableName
ADD
CONSTRAINT [TableName_PK] PRIMARY KEY clustered (ID)
GO
Could it be because the table being moved contains a column with the image data type that is causing the issue?
Any assistance you can provide would be very much appreciated!
June 18, 2008 at 5:44 am
IMAGE data is not stored in the data pages of the table, so moving the clustered index (which is the actual table data) does not move your IMAGE data.
If memory serves me, you actually have to create a new table, copy all of your table data into it, drop the old table, and rename the new one. I don't seem to remember another option for moving blob data to a new filegroup.
You may want to try to change the filegroup on the table in Management Studio and click the generate script button rather than saving the change. It should produce the script to copy the table.
June 18, 2008 at 5:55 am
Hi,
Thanks for your comments.
I wanted to avoid copying the table because of concerns that the database application may change the data during the copy process. Ideally I would like to avoid bringing the application down during this process.
That said, if I were to encapsulate the entire process within a transaction will this ensure the consistency of the data?
Many Thanks,
John
June 18, 2008 at 6:03 am
Yes, the scripting that Management Studio generates does. Basically, it just does a table lock on the table you are moving so all other processes will be blocked during the operation.
Moving a clustered index does the same thing, the table will be locked during the operation. So, if your table had moved with your original operation, it would have been inaccessible for the time it took to move it. This operation is going to take some time, so be prepared to have your application down for a bit.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply