데이타베이스 사용량 확인

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 );

로그파일(LDF) 크기 : log size(MB)

SELECT SUM(CAST(df.size as float))/128 FROM sys.database_files AS df WHERE df.type in ( 1,3 );

할당된용량 : SpaceUsed(MB)

SELECT sum(total_pages)/128 FROM sys.allocation_units;

테이블명 할당되어 있는 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
;