Files and Filegroups

Question

Files and Filegroups

r5d4

SSCrazy

Points: 2523
More actions
May 27, 2010 at 3:12 am

#140624

How can i tell which FILE my indexes/tables are stored in?
Before everyone shouts, there are plenty of scripts that explore PARTITIONS and FILEGROUPS usage.
If i have multiple FILES assigned to a FILEGROUP, how can i determine the allocation of objects within a FILE?
thanks for your time sql people!
r

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

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

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

r5d4 (5/27/2010)
If i have multiple FILES assigned to a FILEGROUP, how can i determine the allocation of objects within a FILE?

Objects are stored in individual filegroups. If there are multiple files in a filegroup, the table will likely be striped across all the files. So it's not in one or the other, it's likely in all of them

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

r5d4 SSCrazy Points: 2523 More actions · Answer 2

Thank you very much for that Gail.

I've managed to add a second file to my PRIMARY file group.

Not very smart, I know.

How Can I remove this file? (Management Studio reports it as being in use).

The following query (from these forums) shows I have nothing in my primary filegroup (I've moved everything to 2 other filegroups 'CoreData' and 'NCIndexes' respectively?

SELECT OBJECT_NAME( i."id" ) AS TableName ,

i."Name" AS IndexName ,

FILEGROUP_NAME( i.groupid ) AS FileGroupName

FROM sysindexes AS i

WHERE ( i.indid IN ( 0 , 1 ) Or i.indid < 255 ) And -- Tables & indexes only

OBJECTPROPERTY( i."id" , 'IsUserTable' ) = 1 And -- User tables only

OBJECTPROPERTY( i."id" , 'IsMSShipped' ) = 0 And -- No system tables

COALESCE( INDEXPROPERTY( i."id" , i."Name" , 'IsStatistics' ) , 0 ) = 0 And -- No Statistics / Auto-Create stats

COALESCE( INDEXPROPERTY( i."id" , i."Name" , 'IsHypothetical' ) , 0 ) = 0 -- No Hypothetical statistics

ORDER BY FileGroupName , TableName , IndexName

thank you

r

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

System tables are stored in the primary filegroup and cannot be moved.

You should be able to remove the second file if you first do a shrinkFile with the EmptyFile option

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

r5d4 SSCrazy Points: 2523 More actions · Answer 4

r5d4

SSCrazy

Points: 2523

June 3, 2010 at 8:24 am

#1175981

Worked perfectly, a big thank you 🙂