Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Which Filegroup Holds My Data?
Published: Jul 03, 2022
Well… now that I’ve got a shiny new website (see previous post)… it’s back to the task of posting about SQL Server again…
So… recently I was asked how to find out what was being stored in a specific filegroup. This came from a situation in which someone was trying to remove a filegroup from their database but was getting an error stating that it wasn’t empty.

Annoyingly there is no easy way to find out what is causing this error to appear, which is why we need to turn to tSQL for the answer…

As you’ll soon discover, the query we need isn’t exactly the most intuitive when it comes to joins which all adds to the issue with finding an answer to the “why can’t I delete my filegroup” question.

Now, I happen not to have any filegroups other than PRIMARY in my AdventureWorks database (okay, okay, please don’t go complaining in the comments… I wouldn’t normally advocate it but this isn’t a Production environment, it’s my laptop with one drive, no need for restores (my AdventureWorks never changes) etc etc.). So let’s create another filegroup and then we’ll create an index on that filegroup:

use [master]

alter database AdventureWorks2012 add filegroup myFilegroup

alter database AdventureWorks2012
add file
name = N'AdventureWorks2012Misc',
filename = N'C:\SQL Server\2016\Data\AdventureWorks2012Misc.ndf',
SIZE = 8192KB ,
to filegroup [myFilegroup]

use AdventureWorks2012
create index ix_miscIndex on sales.salesOrderDetailEnlarged(orderQty)
on [myFileGroup]

Okay, so now we’ve done that, let’s have a look and see if we can find any objects in that filegroup:

select s.name schemaName, t.name tableName, i.name indexName, p.partition_number, fg.name fgName, p.rows
from sys.schemas s
join sys.tables t
on s.schema_id = t.schema_id
join sys.indexes i
on t.object_id = i.object_id
join sys.partitions p
on i.object_id = p.object_id
and i.index_id = p.index_id
left join sys.partition_schemes ps
on i.data_space_id = ps.data_space_id
left join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id
and p.partition_number = dds.destination_id
left join sys.filegroups fg
on coalesce(dds.data_space_id, i.data_space_id) = fg.data_space_id
where fg.name = 'myFileGroup'

As you can see, that’s not the most intuitive query you’ll ever write due to the views and join clauses required, but it does give you the required answer which can be more than helpful at times.

Now, if we want to delete this filegroup, all we need to do is rebuild the index onto another filegroup and we’re all set.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.