Moving Large Table to Different File Group

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Raj Gujar » Moving Large Table to Different File Group

29 posts, Page 3 of 3

«««1 2 3

Moving Large Table to Different File Group

Rate Topic

Display Mode

Topic Options

Author

Message

Cem Uney

Posted Friday, October 17, 2008 12:32 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 04, 2013 8:13 AM
Points: 45, Visits: 128

What happens if there is a ForeignKey which refers this column in another table.
Will there be any problem?

Post #587904

SQLBOT

Posted Friday, October 17, 2008 12:43 PM

SSChasing Mays

Group: General Forum Members
Last Login: Monday, February 18, 2013 3:22 PM
Points: 626, Visits: 835

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

brendanc

Posted Tuesday, October 21, 2008 12:01 AM

Grasshopper

Group: General Forum Members
Last Login: Today @ 2:32 AM
Points: 22, Visits: 195

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

vvkp@hotmail.com

Posted Friday, October 24, 2008 9:41 AM

Forum 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

TheSQLGuru

Posted Friday, October 24, 2008 10:23 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 3,581, Visits: 5,123

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

vvkp@hotmail.com

Posted Friday, October 24, 2008 4:22 PM

Forum 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

TheSQLGuru

Posted Saturday, October 25, 2008 12:28 PM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 3,581, Visits: 5,123

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

vvkp

Posted Thursday, October 30, 2008 7:06 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 6:43 PM
Points: 11, Visits: 135

Thank you very much for your valuable reply ... it helps
Krishna Prasad

Post #594257

hammad772001

Posted Wednesday, June 08, 2011 5:52 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, November 02, 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? Smile

Best,
Hammad

Post #1121652

« Prev Topic | Next Topic »

29 posts, Page 3 of 3

«««1 2 3

Permissions