데이터파일(mdf,ndf) 및 로그파일(ldf) 사용량 확인

DMV를 사용한 데이터베이스 사이즈 체크

SELECT @@VERSION : Microsoft SQL Server 2008 (RTM)

 

USE TESTDB

GO

SELECT

       DB_NAME() AS 'DB 명'

       ,d.name AS '파일그룹명'

       ,e.tSize AS '전체사이즈(mb)'

       ,SUM(used_page_count)*8 /1024.0 AS '현재사이즈(mb)'

       ,e.tSize-SUM(used_page_count)*8 /1024.0 AS '남은사이즈(mb)'

       ,cast((SUM(used_page_count)*8/1024.0)*100/e.tSize AS DECIMAL(10,2))AS '사용량(%)'

FROM sys.objects AS a  JOIN sys.dm_db_partitiON_stats AS b

ON a.object_id = b.object_id

JOIN  sys.sysindexes AS c

ON a.object_id = c.id

AND b.index_id = c.indid

JOIN sys.filegroups AS d

ON c.groupid = d.data_space_id

JOIN (SELECT data_space_id,SUM(size)*8/1024.0 AS 'tSize'

             FROM sys.databASe_files WHERE data_space_id <> 0 GROUP BY data_space_id) AS e

ON d.data_space_id = e.data_space_id        

GROUP BY d.name,c.groupid,e.tSize