Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Moving Large Table to Different File Group Expand / Collapse
Author
Message
Posted Friday, October 17, 2008 12:32 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 8:35 AM
Points: 45, Visits: 135
What happens if there is a ForeignKey which refers this column in another table.
Will there be any problem?
Post #587904
Posted Friday, October 17, 2008 12:43 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, 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
Post #587911
Posted Tuesday, October 21, 2008 12:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 17, 2014 8:04 AM
Points: 22, Visits: 212
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
Post #588953
Posted Friday, October 24, 2008 9:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 14, 2009 10:32 AM
Points: 5, 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
Post #591316
Posted Friday, October 24, 2008 10:23 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 4,320, Visits: 6,113
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 at GMail
Post #591352
Posted Friday, October 24, 2008 4:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 14, 2009 10:32 AM
Points: 5, Visits: 62
Can you post any script for that purpose please. Appreciate your help.


krishnaprasad
Post #591582
Posted Saturday, October 25, 2008 12:28 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:19 AM
Points: 4,320, Visits: 6,113
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 at GMail
Post #591719
Posted Thursday, October 30, 2008 7:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 4, 2014 2:02 PM
Points: 11, Visits: 142
Thank you very much for your valuable reply ... it helps
Krishna Prasad



Post #594257
Posted Wednesday, June 8, 2011 5:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 2, 2012 5:15 AM
Points: 12, Visits: 39
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
Post #1121652
Posted Friday, December 6, 2013 4:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:09 AM
Points: 6,170, Visits: 13,311
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"
Post #1520506
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse