Eliminate FileGroup and move contents to Primary FG

Question

Eliminate FileGroup and move contents to Primary FG

Medeoker

SSC Rookie

Points: 38
More actions
March 30, 2013 at 9:24 pm

#270489

Hi there,
I'm looking for some guidance on how to resolve something I've been tasked with.
I am working on a DB that has a Primary FG and 1 User Defined FG.
I am looking to consolidate all the data that is stored in the User Defined FG into the Primary FG and remove any remnants of the User Defined FG from the Database.
Any assistance someone could provide me would be greatly appreciated!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You'll need to rebuild all the indexes that are on that filegroup ON PRIMARY. That will move them to the primary filegroup. Once empty, you can use ALTER DATABASE to remove the files in that filegroup.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Medeoker SSC Rookie Points: 38 More actions · Answer 2

Thank you for the reply.

Can you point me into the direction of an example of a script that could do this? Is this a good example to follow to get this accomplished? Ms Example

In the Tsql example at the bottom, it looks as though it's only adding the single index, where as I have a few hundred that need to move over. I'm looking for the most automated approach possible. I have very little sql experience, and this is a one time event, which is why I'm looking for help here.

Thanks again!

Medeoker SSC Rookie Points: 38 More actions · Answer 3

I can script creating one index at a time with the code below, but I am looking to find a way to script moving them all in one swoop. I must be missing something very simple. I would rather not create them one at a time.

USE [database]

GO

CREATE NONCLUSTERED INDEX [idxname] ON [dbo].[tablename](colname)

WITH (DROP_EXISTING = ON, ONLINE = OFF) ON [PRIMARY]

GO

joeroshan SSChampion Points: 10377 More actions · Answer 4

Hi, you can acheive this through dynamic scripts. First you should find out the list of indexes in the particular filegroup. You can use sys.partitions to get this data.

select Object_ID,Index_id from sys.partitions P

join sys.allocation_units A

on P.hobt_id=A.Container_id

Join sys.filegroups F

on F.data_space_id=A.Data_space_id

and F.Name='YourFG'

There are many scripts available in here to script out the indexes example

http://www.sqlservercentral.com/scripts/Indexing/70737/

You should change the script to include just the indexes above (write a join with probably a temp table that has the above details)

Also you need to include on Filegroup clause

Sice you mentioned you are relatively new I would like you to play a while with a dev box with good backups until sure what all the code does.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Medeoker SSC Rookie Points: 38 More actions · Answer 5

Thanks Roshan for the help! It took me a while to wrap my brain around the script. After making a few changes, it gave me an output of exactly what I was looking for. [Don't worry, I used a test environment. :)]

Thanks again!