September 10, 2002 at 5:36 pm
Hello everyone, I have a data file on drive H: and it is full, I have added another data file to that filegroup on another drive. How can I move some of the data to the new file? Thank you.
September 10, 2002 at 5:52 pm
Straight from BOL (see below). Also if I remember correctly you can drop the clustered index, and then recreate it on the new filegroup. This will move the clustered index and data for the table to the new filegroup. Hope this helps point you in the right direction.
How to place an existing table on a different filegroup (Enterprise Manager)
To place an existing table on a different filegroup
Expand a server group, and then expand a server.
Expand Databases, expand the database in which the table belongs, and then click Tables.
In the details pane, right-click the table, and then click Design Table.
Right-click any column, and then click Properties.
On the Tables tab, in the Table Filegroup list, select the filegroup on which to place the table.
Optionally, in the Text Filegroup list, select a filegroup on which to place any text, image, and ntext columns.
----------------------------
How to place an existing index on a different filegroup (Enterprise Manager)
To place an existing index on a different filegroup
Expand a server group, and then expand a server.
Expand Databases, expand the database in which the table containing the index belongs, and then click Tables.
In the details pane, right-click the table, and then click Design Table.
Right-click any column, and then click Properties.
On the Indexes/Keys tab, in the Selected index list, select the index to move.
In the Index Filegroup list, select a filegroup on which to place the index.
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 11, 2002 at 7:06 pm
Hi:
With DBCC ShrinkFile (File_name, TruncateOnly) you can migrate all the data pages from the file, to new files in the same filegroup. The file you specified remains empty (unless is the primary file) and spred all the data to other files.
You can create several datafiles in the same filegroup and run the dbcc command to spred the info and then remove the fist data file.
September 12, 2002 at 11:55 am
Thanks for the info. I created another filegroup and moved data to that filegroup. However, I tried to move the indexes to another filegroup on another drive (by using EM) and the size of my index files are not changing. I have moved several big size indexes and nothing has happened. Any idea why? I go to EM and right click on a table that has the index, and choose "manage indexes" and change the filegroup from there. Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply