목차

데이타베이스 사용량 확인

데이타베이스

  1. DATABASE 크기 : database size(MB)
    SELECT SUM(CAST(df.size as float))/128 FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 );
  2. 로그파일(LDF) 크기 : log size(MB)
    SELECT SUM(CAST(df.size as float))/128 FROM sys.database_files AS df WHERE df.type in ( 1,3 );
  3. 할당된용량 : SpaceUsed(MB)
    SELECT sum(total_pages)/128 FROM sys.allocation_units;

테이블 사용량

  1. 테이블명 할당되어 있는 index space와 table space 사용량을 확인 : size(MB)
    -- 
    -- Tables Used 
    -- 
    DECLARE @PageSize FLOAT 
    SELECT @PageSize=v.low/1024.0 FROM master.dbo.spt_values v WHERE v.number=1 AND v.type='E'
     
    SELECT
    	tbl.name,
    	ISNULL
    	(
    		(
    		SELECT @PageSize * SUM
    					(
    					a.used_pages - CASE 
    							WHEN a.type <> 1 THEN 
    								a.used_pages 
    							WHEN p.index_id < 2 THEN 
    								a.data_pages 
    							ELSE 0 END
    					) 
    		FROM sys.indexes AS i
    			JOIN sys.partitions AS p 
    				ON p.object_id = i.object_id AND p.index_id = i.index_id
    			JOIN sys.allocation_units AS a 
    				ON a.container_id = p.partition_id
    		WHERE i.object_id = tbl.object_id
    		)
    		,0.0
    	) AS [IndexSpaceUsed],
    	ISNULL 
    	(
    		(
    		SELECT @PageSize * SUM
    					(
    					CASE 
    						WHEN a.type <> 1 THEN 
    							a.used_pages 
    						WHEN p.index_id < 2 THEN 
    							a.data_pages 
    						ELSE 0 END
    					) 
    		FROM sys.indexes AS i
    			JOIN sys.partitions AS p 
    				ON p.object_id = i.object_id AND p.index_id = i.index_id
    			JOIN sys.allocation_units AS a 
    				ON a.container_id = p.partition_id
    		WHERE i.object_id = tbl.object_id
    		)
    		,0.0
    	) AS [DataSpaceUsed]
    FROM
    sys.tables AS tbl
    ;