January 4, 2011 at 3:13 pm
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!
January 4, 2011 at 3:40 pm
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.
---------------------------------------------------------------------
January 5, 2011 at 9:50 am
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!
January 5, 2011 at 10:35 am
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.
---------------------------------------------------------------------
January 5, 2011 at 10:35 am
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
January 5, 2011 at 10:40 am
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!
January 5, 2011 at 2:52 pm
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.
---------------------------------------------------------------------
January 6, 2011 at 6:33 am
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!
October 23, 2012 at 7:21 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply