mssql 사용량 조사
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;
사이즈 조사
select
f.type_desc as Type
,f.name as FileName
,fg.name as FileGroup
,f.physical_name as Path
,f.size / 128.0 as CurrentSizeMB
,f.size / 128.0 - convert(int,fileproperty(f.name,'SpaceUsed')) / 128.0 as FreeSpaceMb
from sys.database_files f with (nolock)
left outer join sys.filegroups fg with (nolock)
on f.data_space_id = fg.data_space_id