파일 그룹별 개체 및 사이즈 확인

2000 Admin에 올린 것과 동일합니다...

 

--파일그룹별 개체정보

SELECT

                           'FileGroup Name' = sfg.GroupName,

                           'Object Name' = CASE si.IndID

                                                                                WHEN 1 THEN so.Name

                                                                                ELSE si.Name END,

                           'Object Type' =   CASE

                                                                                WHEN si.IndID < 2 THEN 'Table'

                                                                                ELSE 'Index'END,

                           'Table Name'=    CASE

                                                                                WHEN si.IndID > 1 THEN so.Name

                                                                                ELSE ' ' END,

                           'System FileName' = sf.Name

FROM sysfilegroups sfg

             INNER JOIN sysfiles sf                   ON sfg.groupid = sf.groupid

             INNER JOIN sysindexes si             ON sfg.groupid = si.groupid

             INNER JOIN sysobjects so            ON si.id = so.id

WHERE so.type = 'U'

             AND si.Name NOT LIKE '#_%' ESCAPE '#'

             AND so.Name NOT IN ('dtproperties')

ORDER BY 1, 3 DESC

GO

 

 

 

 

 

--파일 그룹별, 파일 수, 사용한 크기, 현재 크기, 최대 크기 출력

SELECT

             a.FileGroupName,

             b.[FileCount],

             a.[UsedSize(MB)],

             b.[FileNowSize(MB)],

             CASE WHEN C.MinCheck = -1 THEN 'Unlimited' ELSE CAST(b.[MaxSize(MB)] AS VARCHAR(100)) END as [MaxSize(MB)]

FROM (

                           SELECT --파일 그룹별 크기(실제 사용 크기)

                                        'FileGroupName' = sfg.GroupName,

                                        sfg.GroupId,

                                        SUM(si.used * 8.0/1024) AS [UsedSize(MB)]

                           FROM sysfilegroups sfg

                                        INNER JOIN sysindexes si ON sfg.groupid = si.groupid

                                        INNER JOIN sysobjects so ON si.id = so.id

                           WHERE so.type = 'U'

                                        AND si.Name not like '#_%' ESCAPE '#'

                                        AND so.Name not in ('dtproperties')

                           GROUP BY sfg.GroupName, sfg.GroupId

             ) a

             INNER JOIN

             (

                           SELECT --파일 그룹별 크기(잡혀있는 크기)

                                        GroupId,

                                        SUM(size*8/1024.0) AS [FileNowSize(MB)],

                                        SUM(maxsize*8/1024.0) AS [MaxSize(MB)],

                                        COUNT(*) AS [FileCount]

                           FROM sysfiles

                           WHERE GroupId>0

                           GROUP BY GroupId

             ) b ON a.GroupId = b.GroupId

             LEFT JOIN

             (

                           SELECT --파일그룹중MaxSize = Limited 인 경우확인

                                        GroupId, MIN(maxsize) as MinCheck

                           FROM sysfiles

                           WHERE GroupId>0

                           GROUP BY GroupId

             ) c ON A.GroupId = c.GroupId

GO