SQL/DBA 가이드

모든 데이터베이스 전체 공간 및 여유 공간 조회

꽁냥꽁냥이 2017. 2. 1. 22:38

 use tempdb

go

 

CREATE TABLE DBO.TBL_DB_SIZE (

       ID           INT IDENTITY,

       NAME   NVARCHAR(100),

       RSVD   NVARCHAR(50),

       DATA   NVARCHAR(50),

       INDX   NVARCHAR(50),

       UNUS   NVARCHAR(50)

)

 

EXEC SP_MSFOREACHDB '

USE [?]

 

 

declare

        @id   int                 -- The object id that takes up space

       ,@type character(2) -- The object type.

       ,@pages      bigint              -- Working variable for size calc.

       ,@dbname sysname

       ,@dbsize bigint

       ,@logsize bigint

       ,@reservedpages  bigint

       ,@usedpages  bigint

       ,@rowCount bigint

 

 

 

select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))

       , @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))

       from dbo.sysfiles

 

select @reservedpages = sum(a.total_pages),

       @usedpages = sum(a.used_pages),

       @pages = sum(

                    CASE

                           -- XML-Index and FT-Index and semantic index internal tables are not considered "data", but is part of "index_size"

                           When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0

                           When a.type <> 1 and p.index_id < 2 Then a.used_pages

                           When p.index_id < 2 Then a.data_pages

                           Else 0

                    END

             )

from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id

       left join sys.internal_tables it on p.object_id = it.object_id

 

 

INSERT INTO TEMPDB.DBO.TBL_DB_SIZE (

       NAME,

       RSVD,

       DATA,

       INDX,

       UNUS

) VALUES (

       ''?'',

       ltrim(str(@reservedpages * 8192 / 1024.,15,0)),

       ltrim(str(@pages * 8192 / 1024.,15,0)),

       ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0)),

       ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0))

)

 

'

 

SELECT * FROM TEMPDB.DBO.TBL_DB_SIZE

 

DROP TABLE TEMPDB.DBO.TBL_DB_SIZE