====== 데이타베이스 사용량 확인 ======

===== 데이타베이스 =====

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


===== 테이블 사용량 =====
  - 테이블명 할당되어 있는 index space와 table space 사용량을 확인 : size(MB) <code sql>
-- 
-- 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
;
</code>