[MS-SQL] MS-SQL Filegroup 사용량 측정 (UI, sql, csv)

MS-SQL에서 Database의 filegroup 사용량을 측정하는 방법에 대해 알아보자.

1. UI

DB (마우스 우클릭) -> 보고서 -> 표준보고서 -> 디스크사용

을 들어가면 

 

위 처럼 단순하게 파이그래프로 filegroup 사용량을 조회할 수 있다.

 

2. sql

select
	b.groupname as 'File group',
	a.name as 'File name',
	a.filename as 'File Path',
	case when maxsize = -1 then 'Unlimited'
		else cast(round(maxsize/128.0, 2) as varchar(20))
		end as 'maxsize(mb)',
	convert(decimal(15,2), round(a.size/128.0, 2)) as 'total(mb)',
	convert(decimal(15,2), round(fileproperty(a.name, 'spaceused')/128.0, 2)) as 'used(mb)',
	convert(decimal(15,2), round((a.size - fileproperty(a.name, 'spaceused'))/128.0, 2)) as 'free(mb)',
	round(fileproperty(a.name, 'spaceused') / convert(float, a.size) * 100, 1) as 'used pct(%)'
from sys.sysfiles a left outer join sys.sysfilegroups b
	on a.groupid = b.groupid
order by b.groupname, a.name;

위 sql문으로 특정 database의 filegroup 사용량을 조회할 수 있다.

 

3. PowerShell script (export csv)

 

$ServerInstance = "MSSQLSERVER"
$Database = "AdventureWorks2022"
$Query = "
		select
			b.groupname as 'File group',
			a.name as 'File name',
			a.filename as 'File Path',
			round(maxsize/128.0, 2) as 'maxsize(mb)',
			convert(decimal(15,2), round(a.size/128.0, 2)) as 'total(mb)',
			convert(decimal(15,2), round(fileproperty(a.name, 'spaceused')/128.0, 2)) as 'used(mb)',
			convert(decimal(15,2), round((a.size - fileproperty(a.name, 'spaceused'))/128.0, 2)) as 'free(mb)',
			round(fileproperty(a.name, 'spaceused') / convert(float, a.size) * 100, 1) as 'used pct(%)'
		from sys.sysfiles a left outer join sys.sysfilegroups b
			on a.groupid = b.groupid
		order by b.groupname, a.name;
		"
$OutputFile = "C:\test\filegroup_usage.csv"


# sql execute and export file
$queryResult = Invoke-Sqlcmd -Database $Database -Query $Query

$queryResult | Export-Csv -Path $OutputFile -NoTypeInformation -Encoding UTF8

이 코드를 작업으로 등록해서 csv로 export해보자

작업 -> 새 작업

작업의 이름을 지정한다.

단계탭에서 '새로만들기'를 누른다.

위와 같이 유형을 'PowerShell'로 고르고 위 powershell스크립트를 입력하고 확인을 누른다.

 

Test로 작업을 돌려본다.

위의 3가지 방법 (UI, sql, powershell)으로 filegroup 사용량을 조회하는 방법에 대해 알아보았다.