SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving Large Table to Different File Group


Moving Large Table to Different File Group

Author
Message
Cem Uney
Cem Uney
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 157
What happens if there is a ForeignKey which refers this column in another table.
Will there be any problem?
SQLBOT
SQLBOT
SSC Eights!
SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)SSC Eights! (984 reputation)

Group: General Forum Members
Points: 984 Visits: 836
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



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
brendanc
brendanc
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 224
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
vvkp@hotmail.com
vvkp@hotmail.com
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 62
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
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12985 Visits: 8565
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
vvkp@hotmail.com
vvkp@hotmail.com
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 62
Can you post any script for that purpose please. Appreciate your help.


krishnaprasad
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12985 Visits: 8565
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
vvkp
vvkp
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 164
Thank you very much for your valuable reply ... it helps
Krishna Prasad



hammad772001
hammad772001
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 67
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
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20696 Visits: 17244
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]



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search