Moving Table to a different File group

Question

Moving Table to a different File group

Davekahn

SSCommitted

Points: 1840
More actions
December 8, 2011 at 12:31 pm

#247491

Following are the steps I have to take in order to move a table to a different file group:
1. create a File group (FG)
2. create a NDF with new File Group (second_FG) Path to new Drive
3. Drop Non cluster Indx
4. Drop F.Key ??
5. Drop Constraints If any?
6. Drop cluster Indx Option
ALTER TABLE dbo.mytablename
DROP CONSTRAINT cndx_PrimaryKey_mykey WITH (MOVE TO FG)
GO
7. ALTER TABLE dbo.mytablename
ADD CONSTRAINT cndx_PrimaryKey_mykey PRIMARY KEY CLUSTERED
(mycolumnname ASC) WITH
(IGNORE_DUP_KEY = OFF) ON FG
GO
I'm missing something? please confirm and help
Thanks
Dave

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135377 More actions · Answer 1

your sequence doesn't contain re-creation of the objects you dropped before altering the CLIX (NCI, constraints, FK-indexes,..)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

iirc you don't need to drop the cluster or the foreign keys or even the nonclusters (though doing so will be faster)

CREATE UNIQUE CLUSTERED INDEX <pk name> ON <table name> (<column definitions>) WITH (DROP_EXISTING = ON) ON <filegroup name>

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Davekahn SSCommitted Points: 1840 More actions · Answer 3

Thanks I just tested it with 20GB Database and it is working now

Yes no no need to drop FK you are right

this morning I was playing with 5MB DB and was not able to figure it out correctly so I asked you all

Thanks for the quick replies

Davekahn SSCommitted Points: 1840 More actions · Answer 4

One more question

Do I need to shrink the DB after completing the task ?

Johan Bijnens SSC Guru Points: 135377 More actions · Answer 5

Now there's a definite "it depends" 😉

Would you expect your db to consume the freed space fairly soon ? Don't shrink it. You'll save the extention overhead if sql needs to auto grow.

If not ... Do you need - really bad - the 20GB on disk for other purposes ? Are you willing to disturb all db consumers to achieve this freeing of space ? If the answer is yes, then shrink by file.

Keep in mind you need to rebuild all indexes that reside in a the filegroup which holds the file you've shrinked.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Davekahn SSCommitted Points: 1840 More actions · Answer 6

Thanks good point, I will keep this in mind

the test DB is 20GB but the Prod is 600GB

Davekahn SSCommitted Points: 1840 More actions · Answer 7

Problem, it is still not solved

After running all the above steps, I checked table properties:

Under table properties --> storage the I can see the file group is "new_FG"

Row count is 18 for the table and Dataspce is 56.273MB

My second file "NDF" is 1MB only and is ref to "New_FG" (new directory path s:\data )

after doing all the steps I checked the folder S:\data size, which is still 1MB?

I add few new records and still the folder size remains same

What I'm missing now?

EXEC sp_help tablename is giving me new file grooup info

sp_spaceused 'tablename' is giving me 18 rows

Please help, I know it's a very stupid mistake by me but what it is ?? please help

Johan Bijnens SSC Guru Points: 135377 More actions · Answer 8

Did you refresh the ssms object browser ?

Can you post the DDL for the used :

- create database

- the concerned table ( and indexes )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Davekahn SSCommitted Points: 1840 More actions · Answer 9

Hi,

Sorry about the late reply,(I was busy with year end work)

well I found the problem , In the table I have a text column 6000 long, Part of the problem

So now I like to discuss how to move Large text/image to a different file group ?

So far I found the following and tested it and it is working

1.-create a new table on the new filegroup

2.-copy the entire table containing (text,image data) to a temporary table

3.-drop the existing table

Couple of question

1st: I like to know if there are any other ways to handle the Large text columns, please share if there is any other way

2nd: I need to know if the company policy is against of recreating tables for vendor related DB's then how to handle the process?

Thanks

azdzn SSCarpal Tunnel Points: 4128 More actions · Answer 10

PLease have a look at this :

http://blog.jitbit.com/2010/04/moving-sql-table-textimage-to-new.html

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

qew420 (1/10/2012)
So far I found the following and tested it and it is working
1.-create a new table on the new filegroup
2.-copy the entire table containing (text,image data) to a temporary table
3.-drop the existing table

Yup, that's it.

1st: I like to know if there are any other ways to handle the Large text columns, please share if there is any other way

No other ways

2nd: I need to know if the company policy is against of recreating tables for vendor related DB's then how to handle the process?

If you can't recreate the table, then you won't be able to move the LOB columns

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Davekahn SSCommitted Points: 1840 More actions · Answer 12

thanks for the quick replies guys, I just tested my work using the below steps and it worked

http://blog.jitbit.com/2010/04/moving-sql-table-textimage-to-new.html

thx again for all your help

Dave