SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving Table to Same Filegroup Different Drive


Moving Table to Same Filegroup Different Drive

Author
Message
Janie.Carlisle
Janie.Carlisle
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 86
Hi,

One of my filegroups is too large for the drive so I need to move a table. The question is can I move a table from one filegroup on one drive to the same filegroup on another drive? I know storage space is cheap and I should just add space to the drive but that is not my decision to make and our IT helpdesk people are outsourced. Thanks!
george sibbald
george sibbald
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13278 Visits: 13695
you cannot explicitly move a specific object between files in the same filegroup, but you could add another file to the filegroup and data would begin to be written to the new file on a proportional fill basis.

You could also create a new filegroup on the new disk and move the table data by rebuilding its clustered index onto the new filegroup.

Perhaps you should take advantage of this situation and think about what would give you the best performance advantage with the extra disk, moving the log file, separating the data from the non-clustered indexes for the table or moving off a table which is frequently joined to other tables.

---------------------------------------------------------------------
Janie.Carlisle
Janie.Carlisle
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 86
Wow! Your solution sounds scary somehow. I guess I will have to give it a shot and see how it goes. I think there are fewer repercussions to consider by moving it to the same filegroup on a different drive but could certainly be wrong. Thanks!
george sibbald
george sibbald
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13278 Visits: 13695
no its not scary, just research files and filegroups (start with books on line) and make sure you take a database backup before you start.

If you want to keep it simple and extending the current drive is a non starter then just add another file to the existing filegroup on the new drive, SQL will immediately start to write to it. However you will have no control on what data is written to the new file and will not be able to move a table to the new file.

If the original file is almost full and also almost fills the drive you will have to turn off autogrow on that file, in which case at some point all data will be written to the new file (presuming no deletes or updates that cause data movement).

If you want to move explicit data then you need a new filegroup.

---------------------------------------------------------------------
GilaMonster
GilaMonster
SSC Guru
SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)SSC Guru (116K reputation)

Group: General Forum Members
Points: 116813 Visits: 45530
You can only explicitly move an object to a filegroup. If there are multiple files in a filegroup, the data will be striped across them all, you cannot explicitly control where a table will go.

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


Janie.Carlisle
Janie.Carlisle
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 86
hmmm I actually like the idea of turning off autogrowth and forcing the new data to the new filegroup. I think I will do that. It doesn't solve the issue of the space being full but it ensures no new data will go there. Thanks!
george sibbald
george sibbald
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13278 Visits: 13695
Janie.Carlisle (1/5/2011)
hmmm I actually like the idea of turning off autogrowth and forcing the new data to the new filegroup. I think I will do that. It doesn't solve the issue of the space being full but it ensures no new data will go there. Thanks!


new file not filegroup. you risk running out of space within your current filegroup that way. You have to add a new file to your current filegroup.

Unless the existing file is almost totally full you cannot completely force all new writes to the new file. If the new file is emptier than the old file more data will be written to the new file, but that is about all you can guarantee. to maximise that effect try and make the new file at least the same size as the old file.

for filegroups with multiple files SQL writes to them in a round robin fashion. So if there are 4 files in a filegroup SQL will write to File A, File B, file C then file D, then back to file A. If one of the files is emptier, proportionally more data will be written to that file. SQL attempts to fix it so that the files become full (and therefore need to grow) at about the same time.

---------------------------------------------------------------------
Janie.Carlisle
Janie.Carlisle
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 86
OK Great! I did it and will turn off autogrowth on the full drive/filegroup to see if anything crashes. I didn't want to do it before I added the new one so I just turned the autogrowth amount to half of what it was. Thanks!
mitph25
mitph25
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 125
all,

The new solution has been discovered.

Actually we can move data between files into same filegroup.

we need to create the new file in the same file group and then restrict the autogrowth to the other files except one that you want to move the data. Or we can do the same with restricting autogrowth of the existing files except that you want to move data.

Once this step finished.....you need to shrinkfile with an emptyfile option which will move the data from one file to another file and make it empty...after that you can keep that file or delete it and enable the autogrowth of the other files...

Regards,
Mitul.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search