October 16, 2008 at 11:17 am
there is a problem (aka, bug) with this if your table has certain qualities.
For example,
I had a table that had a text field, so it had LOB data - I converted it to varchar(max). After this, I then tried to move it to a different filegroup as show in this article, however, the LOB data still stays on the old filegroup. If anyone else has experienced this and found a workaround other than selecting all the old data out into a new table please let me know..
Thanks
Robert
October 16, 2008 at 11:27 am
rtowne (10/16/2008)
there is a problem (aka, bug) with this if your table has certain qualities.For example,
I had a table that had a text field, so it had LOB data - I converted it to varchar(max). After this, I then tried to move it to a different filegroup as show in this article, however, the LOB data still stays on the old filegroup. If anyone else has experienced this and found a workaround other than selecting all the old data out into a new table please let me know..
Thanks
Robert
That's not a bug, but a lack of control over the BLOB/statistics data placement.
Or, from other hand :), all that Clustered Index recreation is just a hack as we were told already up above in this discussion.
It is quite unusual requirement to move tables between the filegroups. Check out the link I gave before - that was a really nasty requirement to re-arrange ALL tables in multiple databases into new filegroups.
But I still had to move tables with BLOB separately. Thanks god SSMS/EM generated the script for those few table in a second for me :).
October 16, 2008 at 1:25 pm
Pam Brisjar (10/16/2008)
This is a hack, plain and simple. If you really want to move the table, the best way is to create a "temp: table on the new filegroup. move the data, drop the existing table, rename the "temp" table and then re-create all of the FK's, constraints, and indexes.It's a bit of a PITA but it's the only way to ensure the whole thing gets moved properly. Besides, that's pretty much what SQL Server is doing behind the scenes anyhow.
By best practices the nc indexes should (probably) be on their own filegroup anyhow but that's a different issue.
Can you please give justification for why you call this a 'hack' and why you feel your mechanism is the 'best way'??
Per 2005 BOL (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/bff3a4bf-4bac-40be-a9bf-e75b1c978711.htm), microsoft recommends this:
Move a table from one filegroup to another by dropping the clustered index and specifying a new filegroup or partition scheme in the MOVE TO clause of the DROP INDEX statement or by using the CREATE INDEX statement with the DROP_EXISTING clause.
That isn't exactly what the author specified, but same intent/results.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 16, 2008 at 3:21 pm
Somebody mentioned the CREATE INDEX WITH DROP EXISTING, and I remember trying that and the answer is yes... and in EE you can do it online... it's pretty sweet... except the necessity of dropping all primary and foreign keys.
At the end of the day you need to do it offline so as to keep referential integrity.
pity.
Craig Outcalt
October 16, 2008 at 8:59 pm
Works only in SQL Server 2005 , doesn't work in 2000.
file size mentioned is very low, including growth rate.
October 17, 2008 at 12:32 pm
What happens if there is a ForeignKey which refers this column in another table.
Will there be any problem?
October 17, 2008 at 12:43 pm
I'm not 100% sure, but I don't think that matters.
The error I was referring to happens when you drop the PK to which the other table's FK's are pointing.
You end up getting a constraint error because, well... you're dropping a PK that has FK's pointed to it, and that's part of the job of constraints.
You would need to drop those other tables' FKs before dropping the PK on the table you want to move.
This is why I said, it's just not practical in reality without an outage.
~Craig
Craig Outcalt
October 21, 2008 at 12:01 am
Hi
To move a table you simply create a clustered index on the file group, if none exists then create one. If you do not want or need a clustered index then simply remove it again after the move.
The thing that I find unnecessary is...
If the move is done with the creation of the clustered index what purpose does the drop constraint with (move to Filegroup) server. You can simply drop the current clustered index and then create it on the new file group.
So could some one explain what purpose the with (move to ) part server.
Books online has this as an explanation
MOVE TO ( partition_scheme_name ( column_name [ 1, ... n] ) | filegroup | "default"}
Specifies a location to move the data rows currently in the leaf level of the clustered index. The table is moved to the new location.
But this would make sense if the constraint was not being drop but merely moved. But seeing as the index is being dropped and then recreated on the new filegroup what purpose does that statement make in this scenario...
I await your feedback....
Thanks
October 24, 2008 at 9:41 am
I have a table (670 million rows) with 200gb on 272 GB drive with clustered index (PK) and another nonclustered index .
I am trying to move the table to another drive which has capcity of 300gb.
I created another Tmp_tablename on the 300gb drive and started inserting rows from original table.
But the problem is I am running out of space on logfile drive ( logfile drive size 400gb and contains only one logfile on that drive) and the database is in simple mode.
krishnaprasad
October 24, 2008 at 10:23 am
This one is easy - do the migration in batches of 100-500K rows at a time with tlog backups after each batch or couple of batches (possibly with truncate_only if you don't care about recovery during move).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 24, 2008 at 4:22 pm
Can you post any script for that purpose please. Appreciate your help.
krishnaprasad
October 25, 2008 at 12:28 pm
assuming you have an identity PK you could do something like this (psuedo-code)
get maxid
@i = minid
while @i < @maxid
begin
begin tran
insert newtable select origtable where id between @i and @i + 500000
check for error
optionally delete from origtable where id between @i and @i + 500000
commit or rollback/abend while
@i = @i + 500000
optionally backup log with truncate_only if don't care about recoverability
end
if you have some non-numeric PK or unique index, simply put TOP 500000 keyfield from origtable into temp table
then do the insert (and optional delete) joining that table to origtable
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 30, 2008 at 7:06 am
Thank you very much for your valuable reply ... it helps
Krishna Prasad
June 8, 2011 at 5:52 am
Great Article, but what if primary constraint has several foreign key constraints from other tables referencing to it? Sometimes the foreign key constraints can be in 100s. Do we have to drop every foreign key constraint from other tables and then move the table?
Also, some tables don't have a primary key but have millions of records. How can we move them?
One thing I'm thinking of is that we script the table to be moved in a new query editor, rename the table name in the script, change the storage options from PRIMARY to SECONDARY, create the new table, insert all the rows from the old table in the new table, drop the old table and rename the new table to the old one.
Any better ideas? 🙂
Best,
Hammad
December 6, 2013 at 4:25 am
ALTER TABLE [INVOICE]
DROP CONSTRAINT [INVOICE_PK] WITH (MOVE TO SECONDERYDATA)
After the move, we now recreate the PK Constraint:
ALTER TABLE [INVOICE]
ADD CONSTRAINT [INVOICE_PK] PRIMARY KEY CLUSTERED
( [column name] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [SECONDERYDATA]
You're specifying the FG name twice. You just need to do this
ALTER TABLE [INVOICE] DROP CONSTRAINT [INVOICE_PK]
ALTER TABLE [INVOICE] ADD CONSTRAINT [INVOICE_PK]
PRIMARY KEY CLUSTERED
(
[column name] ASC
)
WITH (options)
ON [newfilegroup][/CODE]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 16 through 30 (of 57 total)
You must be logged in to reply to this topic. Login to reply