How to move tables from Primary data file to secondary data file

Question

How to move tables from Primary data file to secondary data file

Rajesh K

SSC Veteran

Points: 259
More actions
December 7, 2006 at 5:42 am

#62393

Hi, We are using SQL Server 2000 SP4 and we have one primary data file of 45 GB size and a log file for a production database. Recently we created a secondary data file in a separate drive since we faced insufficient disk space problem with the primary data file. So I thought of moving some huge tables to the secondary data file. But i could not find the concerned commands/scripts to move the tables from primary file to secondary data file.Kindly suggest me on this how i can proceed further or else is there any way to solve this space constraint problem of the primary file?
Thanks in advance!
RAJESH K

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

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

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 1

One way to do it is to recreate the clustered index on the new file. You should be able to go into manage indexes and change the location. You could also drop them, and then recreate them on the other file system.

Tom

bbop1322 SSCommitted Points: 1714 More actions · Answer 2

Hi,

In BOL, search for placing tables on filegroups and it explains how to move existing tables to other filegroups using EM.

hth,

brian

Think great, be great!

Steve Jones - SSC Editor SSC Guru Points: 735932 More actions · Answer 3

The clustered indexes are what hold the data, so as mentioned above, move them.

MohammedU SSCoach Points: 19644 More actions · Answer 4

As Steve mentioned creating clustered index on the new filegroup will move the data(with clustered index) but not the non clustered index.

You need to move non clustered indexes seprate using create.

Note: During the operation table will not be accessible...

1. Drop the non clustered indexes

2. Create clustered index using new filegroup with drop_existing option or sort in tempdb option...

3.Recreate non clustered indexes dropped in step1 on new filegroup if you want to move them to new filegroup.

MohammedU
Microsoft SQL Server MVP

Rajesh K SSC Veteran Points: 259 More actions · Answer 5

Hi, Thanks for your reply. Could you please explain where i can find these steps. I am sorry i cant get what exactly BOL stands for. It will be really greatful if you sent that link to me.

RAJESH K

Steve Jones - SSC Editor SSC Guru Points: 735932 More actions · Answer 6

BOL is Books Online and it's the help file that's in your SQL Server Program Files group (from the Start Menu). Here's the online CREATE INDEX reference: http://msdn2.microsoft.com/en-us/library/ms188783.aspx

Now if I have this table:

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

create table MyTable (MyID int)

go

create clustered index MyTable_idx on dbo.MyTable(MyID)

go

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

and I want to move it from the Primary filegroup (named Primary) to another one (named Second), I do this:

-----

create clustered index MyTable_idx on MyTable(MyID) with drop_existing on [second]

-----

If I now check in EM for the table properties, I'll see the table is on the filegroup "second"

Jeff Gray SSChampion Points: 10667 More actions · Answer 7

Rajesh,

Did you create another file in the same filegroup, or create a new file and filegroup?

The other posts in this thread appear to deal mostly with moving tables/data to different filegroups, not different files in the same filegroup.

If you want to move data from one file to another in the same filegroup, I think you can use DBCC SHRINKFILE.

In order to "control" where a particular table is stored, you should probably use filegroups.

jg

ShekharNaidu SSCrazy Points: 2092 More actions · Answer 8

you can move the tables from one filegroup to other using Enterprise Manager

->RightClick on Table

->goto design table

->right click on any feild

->goto properties

-> change Table FileGropup

->close

you can even get the script how EM is changing the filegroup by clicking YES in the subsequent wizards which you get after clicking on close

Thanks ,

Shekhar

query builder Valued Member Points: 55 More actions · Answer 9

I am trying to accomplish the same task. I have created a secondary file group. I have a lot of tables and God knows how many indexes to each of those tables. Is there an easy way to accomplish this rather than running it individually? Please advise..

Thanks in advance..

MohammedU SSCoach Points: 19644 More actions · Answer 10

You make use of the following scripts with SP_MSFOREACHTABLE procedure to do it all at once....

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1541

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1526

MohammedU
Microsoft SQL Server MVP

Ronald San Juan SSCrazy Points: 2937 More actions · Answer 11

This is a fairly small database. You DO NOT NEED A FILEGROUP. You only need the filegroup if you explicitly want a particular table on the other file. In your case, I believe you only added a new (secondary) file to add space for your DB. The DBCC SHRINKDATABASE will spread your data accross both files (This is what you want). Data from one table will be split on both files. As for file groups, You can control the location of a database object like seggregating the large "History Table" to optimize your backup solution.

Ronald San Juan | SQL DBA
ID 710124 ~ Code "Northwind"