March 30, 2013 at 9:24 pm
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!
March 31, 2013 at 2:31 am
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
March 31, 2013 at 6:09 pm
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!
March 31, 2013 at 8:10 pm
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
March 31, 2013 at 9:18 pm
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.
April 1, 2013 at 8:26 pm
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!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy