Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rbarlow
Starting Member
26 Posts |
 Posted - 2011-04-04 : 16:26:47
|
| I'm trying to change the filegroup that a partitioned table is using. I've been able to switch out, merge, split and switch in all of the partitions, but I can't drop the old filegroup because the Parition 1 (-infinity) is still referencing it.Here are the current Partition Scheme and Function definitions:CREATE PARTITION FUNCTION PF_Test(int) AS RANGE RIGHT FOR VALUES (2009, 2010, 2011)GOCREATE PARTITION SCHEME PS_TestAS PARTITION PF_Test TO (OLD, NEW, NEW, NEW)GOEven if I MERGE RANGE (2009) and put it back in the NEW filegroup, the farmost left filegroup in the Scheme is still OLD.The only way I can think of is to switch out all the tables, move them, drop the original Scheme/Function and recreate them and switch back in the data, but I would like to avoid this if possible. :) |
|
|
JeffK95z
Starting Member
19 Posts |
Posted - 2013-05-09 : 17:30:51
|
| really old topic, but i have the same issue...No matter what I do, i can't seem to get rid of the 'first' partitions orginal/old filegroup.I have been able to move (switch/rebuild/merge/split/switch back) every other partition, and there are 0 rows left on this first partition.but i just can't get rid of the org. filegroup!anyone have any ideas? |
 |
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-05-10 : 03:23:42
|
| Questions:Why do you want to drop?Why can't you reuse them?Dropping partitions is not a good idea and it isn't a good practice either. Please reuse partitions rather than dropping, there must be a way to drop but not sure and not best practice either.Thanks,srimami. |
|
|
|
JeffK95z
Starting Member
19 Posts |
Posted - 2013-05-10 : 06:13:47
|
| Why remove?We have redesigned our SAN/Filegroup layout and are testing the migration of all objects (everything but partitioned tables is easy).Why not re-use?We wish to drop the filegroup once we have moved everything away from it. Or another example of this would be if someone had created the partitioned table in the primary Filegroup and wanted to follow best practices and move everything out of that FG. |
|
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-05-10 : 09:23:25
|
| You can try sliding partitions and when the data is empty from partitions, you can go with DBCC shrinkfile (datafilename, empty) and remove the datafiles from disk drive (be careful though). |
|
|
|
JeffK95z
Starting Member
19 Posts |
Posted - 2013-05-10 : 09:36:10
|
| Sadly it doesn't work... i've done the sliding window to clear everything from it already :(here's a great example:http://iunite.com.au/wp/blog/index.php/2012/04/the-filegroup-cannot-be-removed-because-it-is-not-empty/#more-274but instead of trying to remove fg3, i'm looking to remove fg1and thanks for the help and suggestions srimami!! |
|
|
|
|
|
|
|
|
Remove Old Partitioned Table Filegroup