[MSSQL] SQL Server 호스트의 Disk Space Alert 설정하기 / Powershell 방식 VS MSSQL 쿼리 방식

 DBA로서, 데이터베이스가 저장되는 Disk의 Space를 관리하는 일은 대단히 중요하다.

데이터 파일이 저장될 Disk의 용량이 부족하다면, 당연하게도 데이터가 계속적으로 증가하는 서비스는 멈추게 된다.

 

 따라서 DBA는 데이터베이스가 저장되는 Disk의 Space를 수시로 확인하여 용량이 부족할 경우, 스토리지 담당자와 컨택하여 최대한 빠르게 스토리지 공간을 늘리는 것이 중요하다. 그러기 위해서는 Disk Space를 수시로 체크하고 DBA와 스토리지 담당자에게 Alert을 보내는 시스템을 구축해야 한다.

 

 이번 포스팅에서 그 Alert 시스템 구축에 대해 간단히 알아보자!

이 포스팅에서는 크게 2가지 방법을 소개한다. 첫번째는 Powershell 스크립트 방법, 두번째는 MSSQL 쿼리를 활용한 방법에 대해 알아볼 것이다. 또한 alert 시스템은 실제 Production 환경에서 5분 단위로 job이 돌게끔 지정하기도 하는데, 이렇게 자주 도는 만큼  서버의 리소스를 많이 차지하면 안되므로, 어떤게 더 리소스 효율적인지도 알아보자!

---

1. Powershell 스크립트 방식

# SQL Server가 설치되어 있는 hostname을 'hostname' 변수에 담기
$hostname=Get-WMIObject Win32_ComputerSystem | Select-Object -ExpandProperty name

# 임계치 설정 (GB)
# powershell의 hashtable 자료형으로 선언. Disk의 Drive Letter와 Threshold 값을 매칭시킨다.
$thresholdList = @{
    # Threshold 값은 각 드라이브 Total Size의 10% 정도로 잡았다.
    C = 20
    D = 50
    K = 15
    L = 10
    M = 0.05
    T = 2.5
    S = 1
}

# 이메일 정보 설정
$smtpServer = "SMTP Server IP"
$smtpPort = [SMTP Server Port]
$username = "SMTP Server Authentication Username"
$password = "SMTP Server Authentication Password"
$from = "Alert 메일의 보내는 이를 지정"
$to = "Alert 메일이 가는 대상을 지정, 여러 명 가능"
$subject = "$hostname : DISK Space Alert"	# $hostname 변수를 사용하여 Alert의 대상이 되는 호스트네임을 메일 제목에 확인하기 쉽게 넣기
$body = ""	# 메일의 내용에 해당하는 부분으로, 아래 foreach 구문 위해 빈 str으로 지정

# 드라이브 목록 설정
$drives = Get-PSDrive -PSProvider FileSystem | Where-Object { $_.Free -ne $null -and $_.Root -notlike '*swdist*' }

# 각 드라이브별 공간 확인
foreach ($drive in $drives) {
    $driveLetter = $drive.Root	# C 드라이브를 예로 들면, 'C:\' 가 'driveLetter' 변수에 담기게 됨.
    $pdriveLetter = $driveLetter[0]	# 'C:\'에서 첫번째 문자인 'C'만 추출.

    $freeSpace = [math]::Round($drive.Free / 1GB, 2)	# GB 단위로 체크할 것이므로, '1GB' 로 나눈다.
    $totalSpace = [math]::Round(($drive.Used + $drive.Free) / 1GB, 2)
    $percentFree = [math]::Round(($freeSpace / $totalSpace) * 100, 2)


    # 각 드라이브별로 threshold 값 체크하여 body 메시지 작성해주기.
    # 드라이브의 남은 용량이 threshold 값보다 작을 경우, body에 메시지를 추가한다.
    if ($freeSpace -lt $thresholdList["$pdriveLetter"]) {
        $body = $body + "`n`n" + "'$hostname' : Drive $driveLetter is running low on disk space (Free: $freeSpace GB, Total: $totalSpace GB, Free %: $percentFree %)"
    }
    # 명목상 powershell 상에서 else문을 타는지 확인하기 위함.
    else {
        Write-Host "'$hostname' : Drive $driveLetter is available."
    }
}

# 'body' 변수가 빈 문자열이 아니라면 이메일 발송
if ($body -ne "") {
    Send-MailMessage -SmtpServer $smtpServer -Port $smtpPort -From $from -To $to -Subject $subject -Body $body    
}

---

2. MSSQL 쿼리 방식

 MSSQL 쿼리 방식은 [msdb] 를 활용한다. 참고로 SQL Server에서 [msdb] 란, SQL Server 엔진을 설치하면 기본으로 생성되는 시스템 데이터베이스로, SQL Server Agent의 Alerts 및 Jobs 를 예약하고, SSMS, Service Broker 및 Database Mail과 같은 기타 기능에 사용되는 데이터베이스이다. 

 

MSSQL 쿼리 방식의 디스크 여유 공간을 확인하는 로직은 Powershell과 비슷하다.

[현재 드라이브들의 남은 용량을 테이블에 기록한다.]

-> [각 드라이브별 Threshold를 기록해놓은 테이블을 참조한다.]

-> [지정해놓은 Threshold보다 드라이브의 남은 용량이 작다면, 메일의 내용 부분에 해당하는 변수에 담는다.]

-> [최종적으로 메일의 내용 부분에 해당하는 변수를 파라미터로, 메일을 전송한다.]

 

위 과정을 수행하기 위해서는 먼저 [msdb]에 테이블을 생성해야 한다.

 

2-1. Disk Space의 Threshold 정보를 지정하여 넣을 테이블 생성 - 'driveThreshold'

/* Disk Space Threshold 테이블 생성 */

USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[driveThreshold](
	[drive_letter] [varchar](1) NOT NULL,
	[total_size_MB] [int] NULL,
	[threshold_size_MB] [int] NULL,
	[is_check] [int] NULL,	-- is_check는 해당 드라이브에 대하여 alert 을 적용할 지 말지 선택할 때 사용한다.
PRIMARY KEY CLUSTERED 
(
	[drive_letter] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[driveThreshold] ADD  DEFAULT ((1)) FOR [is_check]
GO

 

2-2. 드라이브별 Threshold 값 지정하여 'diskThreshold' 테이블에 넣기

우선, OS의 각 드라이브별 총 사이즈를 확인하기 위해 다음 쿼리 'xp_cmdshell' 을 이용할 건데, 이는 sp_configure를 통해 xp_cmdshell 이 수행되도록 전역 설정 변경이 필요하다. 아래 쿼리를 수행하여 xp_cmdshell 을 enable 설정하자. 보안상 계속 enable 해두는 것은 위험하므로, 이 2-2. 마무리 단계에 xp_cmdshell 을 다시 disable 할 것이다.

EXECUTE sp_configure 'show advanced options', 1
GO
RECONFIGURE 	-- reconfigure 명령어는 'ALTER SETTINGS' 서버 수준 권한 또는  sysadmin 및 serveradmin 고정 서버 역할이 필요하다.
GO

EXECUTE sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE
GO

EXECUTE sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

 

OS에 mount 되어 있는 drive 의 총 사이즈 (=Capacity), 사용 가능 공간 (=Free Space) 로 임시테이블 생성

DECLARE @svrName VARCHAR(255)
DECLARE @sql VARCHAR(400)

SET @svrName = @@SERVERNAME
SET @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

--creating a temporary table
CREATE TABLE #output
(line varchar(255))

--inserting disk name, total space and free space value in to temporary table
INSERT INTO #output
EXEC xp_cmdshell @sql

GO

/* diskThreshold 테이블에 각 드라이브별 Threshold 값 지정  */

-- 위 #output 임시 테이블 통해 드라이브별 총 사이즈를 'driveThreshold' 테이블에 삽입
INSERT INTO [msdb].[dbo].[driveThreshold] ([drive_letter], [total_size_MB])
--script to retrieve the values in MB from PS Script output
	SELECT RTRIM(LTRIM(SUBSTRING(line,1,CHARINDEX('|',line) -3)))		AS [drive_name]
	   , ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('|',line)+1,
	   (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) AS Float),0)	AS [Capacity (MB)]
	FROM #output
	WHERE line like '[A-Z][:]%'
	ORDER BY [drive_name]

-- driveThreshold 테이블 데이터 Insert 잘 되었는지 확인
SELECT * FROM [msdb].[dbo].[driveThreshold]

-- Threshold 값 일괄적으로 각 드라이브의 Total Size * 0.1 (10%) 로 변경하기
UPDATE [msdb].[dbo].[driveThreshold]
SET [threshold_size_MB] = [total_size_MB] * 0.1

--'driveThreshold' 테이블 값 삽입하기 위해 만들었던 임시테이블 삭제
DROP TABLE #output

GO

xp_cmdshell disable 설정

EXECUTE sp_configure 'show advanced options', 1
GO
RECONFIGURE 	-- reconfigure 명령어는 'ALTER SETTINGS' 서버 수준 권한 또는  sysadmin 및 serveradmin 고정 서버 역할이 필요하다.
GO

EXECUTE sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE
GO

EXECUTE sp_configure 'show advanced options', 0
GO
RECONFIGURE
GO

 

2-3. 쿼리가 수행되는 순간의 각 드라이브 여유 공간을 기록할 테이블 생성 - 'driveAvailable'

/* 현재 드라이브의 남은 용량을 기록할 테이블('driveAvailable') 생성 */

USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[driveAvailable](
	[id] [int] IDENTITY(1,1) NOT NULL,		-- 드라이브 사이즈 기록의 순번 용도
	[drive_letter] [varchar](1) NULL,
	[drive_available_MB] [int] NULL,
	[datetime] [datetime] NULL,				-- 드라이브 사이즈를 체크한 순간의 시간 기록
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[driveAvailable] ADD  DEFAULT (getdate()) FOR [datetime]
GO

 

2-4. SQL Server Agent의 Job 으로 등록할 쿼리 (Job step의 Type을 T-SQL로 지정)

USE [msdb]

SET NOCOUNT ON	-- 쿼리 수행결과 중 영향받은 행 수를 나타내는 메시지 반환을 하지 않겠다.

DECLARE @subjectstr varchar(100)
	, @querystr varchar(1000)
	, @limit int, @rowcnt int

INSERT INTO msdb.dbo.driveAvailable(drive_letter, drive_available_MB) 
EXEC xp_fixeddrives

SET @rowcnt = @@rowcount

SET @subjectstr = @@servername + ': DISK Space Alert'	--메일 제목
SET @querystr ='
SELECT ''"'' + @@servername +''": ''+ da.drive_letter + '' drive is '' + convert(varchar(10), convert(numeric(5,2), ((drive_available_MB/(total_size_MB * 1.0)) * 100))) + '' % remained. ('' + convert(varchar(10), drive_available_MB) + '' MB available)''
FROM msdb.dbo.driveThreshold t 
	JOIN (
	    SELECT TOP (' + CONVERT(VARCHAR(10), @rowcnt) + ') * 
	    FROM msdb.dbo.driveAvailable ORDER BY id DESC
	    ) da 
	ON t.drive_letter = da.drive_letter
WHERE is_check = 1
	AND da.drive_available_MB < t.threshold_size_MB
' -- 메일 내용에 해당


IF(EXISTS(
	SELECT *
	FROM msdb.dbo.driveThreshold t 
		JOIN (SELECT TOP (@rowcnt) * FROM msdb.dbo.driveAvailable ORDER BY id DESC) da ON t.drive_letter = da.drive_letter
	WHERE t.is_check = 1
		AND da.drive_available_MB < t.threshold_size_MB
		)
)
BEGIN
	EXEC msdb.dbo.sp_send_dbmail  
		@profile_name = 'SSMS Database Mail Profile',		-- SSMS 상의 Database Mail 에 설정되어 있는 Profile명
		@recipients = 'alert 메일 갈 메일 주소. 여러개 가능.',	-- alert 메일이 갈 대상
		@query =  @querystr,  
		@subject = @subjectstr,  
		@attach_query_result_as_file = 0
END

 

 

2.5. 매일 일정한 시간에 따라 변화된 Disk Space 값 조회

SELECT [id]
	, [drive_letter]
	, [drive_available_MB]
	, [datetime]
FROM [msdb].[dbo].[driveAvailable]
WHERE [drive_letter] = 'F'
	AND [datetime] >= DATEADD(DAY, -100, GETDATE())	-- 최근 100일 동안의 데이터
	AND CONVERT(VARCHAR, [datetime], 8) BETWEEN '08:00:00.000' AND '08:05:00.000' -- 5분 단위로 Disk Space를 기록한다고 했을 때, 매일 8시 00분대의 데이터 조회 위함 
ORDER BY [datetime] DESC

---

3. 결론

 Intel Xeon의 8코어(logical 16코어) CPU로 테스트 해본 결과, Windows의 Performance Monitor의 %Processor Time 메트릭 기준으로, Powershell 스크립트 기반으로 동작하는 Agent Job은 순간적으로 5%~7% 가량의 CPU 부하율을 보였고,

T-SQL 스크립트 기반으로 동작하는 Agent Job은 순간적으로 1~3% 가량의 CPU 부하율을 보였다.

 단순 Percentage 값으로 보면 최대 6% 차이이지만, 이러한 job을 수많은 트랜잭션이 오고 가는 Production 서버에서 5분 단위로 수행한다면, 이 6%가 어마어마한 차이를 낼 수도 있을 것이라 생각한다.

 따라서, SQL Server 의 데이터 파일이 차지하는 드라이브의 Disk Alert을 보내는 시스템은 가능하면 위 두 방법 중 T-SQL 기반으로 하는 방법을 채택하는 게 올바른 것으로 보인다.

 

혹여나 Disk 의 현재 용량을 측정하고 기록해두는 테이블(driveAvailable) 자체가 차지하는 용량이 많지 않을까도 염려스러웠는데, 계산해보면 이렇다. 

• id - int : 4바이트
• drive_letter - varchar(1) : 1바이트
• drive_available_MB - int : 4바이트
• datetime - datetime : 8바이트

한 Row 당(한 드라이브에 대한 정보) 총 17바이트로, 위 예시에서는 체크하는 드라이브 개수가 6개, 이를 5분 단위로 한다고 했을 경우 1시간에 12번 job이 돌고, 이를 1년으로 환산할 경우의 driveAvailable 테이블 용량은 다음과 같다.

(17*6) * 12 * 24 * 365 = 10,722,240 바이트 = 10.22 MB 이다.

 즉, 이러한 job을 Windows의 Drive Letter 최대 개수인 26개로 잡고, 10년동안 돌려도 500 MB 가 안된다. 그러니 'driveAvailable' 테이블이 차지하는 용량은 신경 쓰지 않아도 된다!

---

참고

https://learn.microsoft.com/ko-kr/sql/relational-databases/databases/msdb-database?view=sql-server-ver16 

msdb 데이터베이스 - SQL Server

https://learn.microsoft.com/ko-kr/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver16 

int, bigint, smallint 및 tinyint(Transact-SQL) - SQL Server

https://www.mssqltips.com/sqlservertip/2444/script-to-get-available-and-free-disk-space-for-sql-server/

Script to Get Available and Free Disk Space for SQL Server