December 5, 2012 at 4:07 pm
I have a table with multiple partitions and multiple file groups per partition. is there a simple way to just change the file group of an existing file? this table is large enough that it would create issues trying to move it at this point. 1 table, 25 files 25 file groups.
.
December 7, 2012 at 1:02 am
fluffydeadangel (12/5/2012)
I have a table with multiple partitions and multiple file groups per partition. is there a simple way to just change the file group of an existing file? this table is large enough that it would create issues trying to move it at this point. 1 table, 25 files 25 file groups.
i didnt get you here . what you are trying to do, your actual requirement or issue ?
moving the data into new location ? means new filegroup (datafile/disk) ? OR renaming the filegroup ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 7, 2012 at 8:21 am
I apologize, let me try this again. I have a single table that is in 25 files across 25 file groups. my issue is the final file is roughly 1.5b rows. i cant just empty the file and have it spread across due to all files having different file groups. I'm wanting to place them all into one file group so that i can manage them easier. I don't mind the multiple files, i just want to consolidate file groups.
.
December 7, 2012 at 8:33 am
Files cannot be moved between filegroups.
If you want to move partitions of a table to a new filegroup, you'll have to create the new filegroup, create files within it then alter (it think it is) the partition scheme to move the partitions to the new filegroups. It won't be a quick operation.
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
December 7, 2012 at 8:48 am
GilaMonster (12/7/2012)
Files cannot be moved between filegroups.If you want to move partitions of a table to a new filegroup, you'll have to create the new filegroup, create files within it then alter (it think it is) the partition scheme to move the partitions to the new filegroups. It won't be a quick operation.
That's what I've found so far. a long and tedious operation. I was still holding onto a glimmer of hope there was a lesser known way around this. Thank you for the responses. Final question if I may. We are moving this from 2005 to 2008. I'm wanting to set page compression on this table. The calculator shows a 60% gain in disk space. Will this allow the table to be copied over to a new location faster? strictly asking I guess, when the data is copied... does it have to uncompressed first or does it move over compressed?
.
December 7, 2012 at 9:04 am
fluffydeadangel (12/7/2012)
Will this allow the table to be copied over to a new location faster? strictly asking I guess, when the data is copied... does it have to uncompressed first or does it move over compressed?
It should be a lot faster. Even if there's some decompression in memory, there's still a major saving in amount of data read, amount written and amount logged (the row-compressed values are logged)
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
December 7, 2012 at 11:33 am
Thank you. I appriciate all of the responses I've gotten. This is of great help.
.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply