Post reply

is there any query that yields which filegroup(s) are currently being used

  • September 8, 2011 at 3:13 pm

    #243004

    Goof afternnon.

    I need your help, id like to know a select statement that prints which fileguups and datafiles are being used ?

    The above question is because our DBA needs to move some objects to a another and new filegroup and i've got to make sure which filegroup and data file is being used before the DBA takes any action to move the objets.

    Id appreciate your help

  • September 8, 2011 at 3:17 pm

    #1378925

    I think this will be of help.

    ;

    with tablesize as (

    select so.Name as TableName

    ,TableSizeMB = convert(decimal(15,2),si.dpages *8 / 1024)

    ,IndexSizeMB = convert(decimal(15,2),sum(isnull(si2.used,0))*8 / 1024)

    ,TotalSizeMB = convert(decimal(15,2),(si.dpages * 8 /1024) + (sum(isnull(si2.used,0))*8 / 1024))

    ,TableFreeMB = convert(decimal(15,2),(si.reserved * 8 /1024) -(si.used * 8/1024))

    ,TableSizeKB = convert(decimal(15,2),si.dpages *8)

    ,IndexSizeKB = convert(decimal(15,2),sum(isnull(si2.used,0))*8)

    ,f.Name as FileGroupName

    ,d.physical_name as FGFileName

    ,(select convert(decimal(15,2),sum(reserved)* 8 /1024) from sysindexes where indid in (0,1)) as DBSize

    from sysindexes si

    Inner join sys.objects so

    on so.object_id = si.id

    and so.is_ms_shipped = 0

    --and so.type = 'U'

    and si.indid in (0,1)

    and so.name <> 'sysdiagrams'

    Inner join sysindexes si2

    on so.object_id = si2.id

    and si2.indid > 1

    and si2.indid < 255

    Inner Join sys.filegroups f

    on f.data_space_id = si.groupid

    Inner Join sys.database_files d

    on f.data_space_id = d.data_space_id

    group by so.Name,si.dpages,f.Name,d.physical_name,si.reserved,si.used

    )

    Select TableName,TableSizeMB,IndexSizeMB,TotalSizeMB,TableFreeMB,TableSizeKB,IndexSizeKB,FileGroupName,FGFileName,DBSize

    ,convert(decimal(15,12),(TableSizeMB + IndexSizeMB)/DBSize) * 100 as TablePercentofDB

    from TableSize

    Order by FileGroupName asc,TableSizeMB desc

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 2 posts - 1 through 1 (of 1 total)

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