Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Moving Table to Same Filegroup Different Drive Expand / Collapse
Author
Message
Posted Tuesday, January 4, 2011 3:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 1:40 PM
Points: 43, 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!
Post #1042728
Posted Tuesday, January 4, 2011 3:40 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:22 AM
Points: 5,888, Visits: 13,061
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.


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

Post #1042740
Posted Wednesday, January 5, 2011 9:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 1:40 PM
Points: 43, 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!
Post #1043170
Posted Wednesday, January 5, 2011 10:35 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:22 AM
Points: 5,888, Visits: 13,061
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.


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

Post #1043205
Posted Wednesday, January 5, 2011 10:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 40,610, Visits: 37,074
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 2008, MVP
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

Post #1043206
Posted Wednesday, January 5, 2011 10:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 1:40 PM
Points: 43, 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!
Post #1043210
Posted Wednesday, January 5, 2011 2:52 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:22 AM
Points: 5,888, Visits: 13,061
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.


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

Post #1043369
Posted Thursday, January 6, 2011 6:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 1:40 PM
Points: 43, 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!
Post #1043700
Posted Tuesday, October 23, 2012 7:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 3:26 PM
Points: 1, Visits: 113
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.
Post #1376026
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse