SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Remove Old Partitioned Table Filegroup
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rbarlow
Starting Member

26 Posts

Posted - 04/04/2011 :  16:26:47  Show Profile  Reply with Quote
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)
GO

CREATE PARTITION SCHEME PS_Test
AS PARTITION PF_Test TO (OLD, NEW, NEW, NEW)
GO

Even 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

Canada
19 Posts

Posted - 05/09/2013 :  17:30:51  Show Profile  Reply with Quote
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?
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 05/10/2013 :  03:23:42  Show Profile  Reply with Quote
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.
Go to Top of Page

JeffK95z
Starting Member

Canada
19 Posts

Posted - 05/10/2013 :  06:13:47  Show Profile  Reply with Quote
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.
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 05/10/2013 :  09:23:25  Show Profile  Reply with Quote
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).
Go to Top of Page

JeffK95z
Starting Member

Canada
19 Posts

Posted - 05/10/2013 :  09:36:10  Show Profile  Reply with Quote
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-274

but instead of trying to remove fg3, i'm looking to remove fg1

and thanks for the help and suggestions srimami!!

Edited by - JeffK95z on 05/10/2013 09:37:03
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000