[MSSQL] 모든 유저 데이터베이스의 파일 사이즈 기록하는 Agent Job 만들기

DB 마이그레이션이나 다른 유지보수성 작업을 할 때,

DB에서의 데이터 사용량이 날마다 얼마나 늘어나는 지 기록해 둘 필요가 있을 수 있다.

 

물론, 요즘엔 모니터링 Tool 이 너무나 잘 나와서 이런 프로시저나 Agent Job 을 굳이 생성해야 하나 싶지만, 

그런 모니터링 Tool 도입이나 사용이 어려운 상황이 있을 수 있으므로,

SQL Server 상에서 아래와 같이 관리용 테이블과 프로시저를 생성하고,

SQL Server Agent Job 을 활용하여 해당 DB의 사이즈들을 기록하는 방법에 대해 알아보자.

---

1. 파일 사이즈 기록해 둘 테이블 [DBFileSizesLog] 생성

-- 여러 관리 작업과 관련된 데이터가 저장되는 곳인 [msdb]에 테이블을 만들기로 했다.
USE [msdb];
GO

/* 각 DB들의 .mdf, .ldf 파일 사이즈를 기록해 둘 테이블 [DBFileSizesLog] 생성 */
CREATE TABLE DBFileSizesLog (
	[Log_Date] DATETIME DEFAULT GETDATE(),
	[DB_Name] NVARCHAR(50),
	[Logical_File_Name] NVARCHAR(50),
	[File_Storage_Path] NVARCHAR(200),
	[Current_File_Size_MB] DECIMAL(18,4),
	[Used_Space_MB] DECIMAL(18,4),
	[Free_Space_MB] DECIMAL(18,4),
	[Incremental_Usage_Daily_MB] DECIMAL(18,4)
);
GO

 

필자가 생성할 테이블 내의 컬럼 설명은 다음과 같다.

• Log_Date: DB 사이즈를 확인하는 쿼리를 수행한 후, INSERT INTO가 되는 순간에 시간이 기록되는 컬럼
• DB_Name: DB의 이름
• Logical_File_Name: DB의 Logical Name
• File_Storage_Path: 해당 DB의 파일이 저장되는 OS상 디스크 경로
• Current_File_Size_MB: 현재 DB가 디스크 상에서 점유 중인 파일의 사이즈
• Used_Space_MB: 디스크 상 점유 중인 파일의 사이즈 중에서 실제로 데이터가 사용 중인 사이즈
• Free_Space_MB: [Current_File_Size_MB] - [Used_Space_MB] 
• Incremental_Usage_Daily_MB: 해당 DB의 (하루 전[Used_Space_MB]) - (오늘 확인된 [Used_Space_MB])

---

1-1. [DBFileSizesLog] 테이블의 사이즈 측정

데이터베이스의 파일 사이즈를 기록하는 테이블 자체 사이즈가 크다면, 배보다 배꼽이 더 커질 수 있으므로,

이 로깅을 위한 테이블의 사이즈가 얼마나 되는 지 가늠해보자.

• Log_Date (DATETIME): 8 bytes
• DB_Name (NVARCHAR(50)): (최대 50 characters) * (2 bytes per character) = 100 bytes
• Logical_File_Name (NVARCHAR(50)) : (최대 50 characters) * (2 bytes per character) = 100 bytes
• File_Storage_Path (NVARCHAR(20)) : (최대 200 characters) * (2 bytes per character) = 400 bytes
• Current_File_Size_MB (DECIMAL(18,4)): 9 bytes
• Used_Space_MB (DECIMAL(18,4)) : 9 bytes
• Free_Space_MB (DECIMAL(18,4)) : 9 bytes
• Incremental_Usage_Daily_MB (DECIMAL(18,4)) : 9 bytes

위 컬럼들의 사이즈를 모두 더하면, 한 Row의 최대 사이즈는 644 bytes 가 된다. 

 

유저 데이터베이스가 대충 10개 정도 있다고 하고, 각 DB당 .mdf 파일 1개, .ndf 파일 10개, .ldf 파일 1개로 가정한다면,

10 * (1+10+1) * 644 bytes = 77,280 bytes = 75.47 KB 가 된다.

 

하루에 1번씩 위 프로시저를 수행하고 이 데이터들의 보관 주기를 2년으로 잡으면,

75.47 KB * 730 = 55,092 KB = 53 MB 정도 되므로, 사이즈를 그리 많이 차지하지 않는다. 

하루에 1시간씩, 총 24번을 로깅한다고 하여도,  1 GB 를 조금 넘는 정도이다.

 

용량을 걱정하고 해당 테이블의 데이터를 주기적으로 삭제할 필요는 없을 듯 하다.

---

 

2. SQL Server Agnet Job으로 수행할 프로시저 생성

아래 쿼리를 먼저 수행하여, 프로시저 안에 있는 @sql 구문이 제대로 동작하는 지부터 확인해 보자!

 USE [사이즈 측정할 DB Name];
 
INSERT INTO [msdb].[dbo].[DBFileSizesLog] (
    [Log_Date], 
    [DB_Name], 
    [Logical_File_Name], 
    [File_Storage_Path],
    [Current_File_Size_MB], 
    [Used_Space_MB], 
    [Free_Space_MB],
    [Incremental_Usage_Daily_MB]
)
SELECT 
	[Log_Date] = GETDATE(), 
	[DB_Name] = DB_NAME(), 
	[Logical_File_Name] = name,
	[File_Storage_Path] = physical_name,
	[Current_File_Size_MB] = size / 128.0, 
	[Used_Space_MB] = CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0, 
	[Free_Space_MB] = size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0,
	[Incremental_Usage_Daily_MB] = COALESCE(
		(CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0) - (
			SELECT TOP 1 [Used_Space_MB]
			FROM [msdb].[dbo].[DBFileSizesLog]
			WHERE [DB_Name] = DB_NAME() AND [Logical_File_Name] = name
			ORDER BY [Log_Date] DESC
		),
		0
	)
FROM [sys].[database_files]
WHERE type IN (0, 1);

 

위 쿼리가 제대로 동작한다면, 아래 쿼리로 생성되는 프로시저도 잘 동작할 것이다.

/* SQL Server Agent Job 으로 수행할 프로시저 생성 */
CREATE PROCEDURE USP_Logging_DB_Sizes
AS
BEGIN

	DECLARE @dbName NVARCHAR(128);
	DECLARE @sql	NVARCHAR(MAX);

	-- Cursor to loop through all database
	DECLARE db_cursor CURSOR FOR
	SELECT name
	FROM sys.databases
	WHERE state_desc = 'ONLINE'
	AND name NOT IN ('master', 'model', 'msdb', 'tempdb');

	OPEN db_cursor;
	FETCH NEXT FROM db_cursor INTO @dbName;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Dynamic SQL to switch to the target database and collect file size information
		SET @sql = '
            USE ' + QUOTENAME(@dbName) + ';
			INSERT INTO [msdb].[dbo].[DBFileSizesLog] (
				[Log_Date], 
				[DB_Name], 
				[Logical_File_Name], 
				[File_Storage_Path],
				[Current_File_Size_MB], 
				[Used_Space_MB], 
				[Free_Space_MB],
				[Incremental_Usage_Daily_MB]
			)
			SELECT 
				[Log_Date] = GETDATE(), 
				[DB_Name] = DB_NAME(), 
				[Logical_File_Name] = name,
				[File_Storage_Path] = physical_name,
				[Current_File_Size_MB] = size / 128.0, 
				[Used_Space_MB] = CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT) / 128.0, 
				[Free_Space_MB] = size / 128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT) / 128.0,
				[Incremental_Usage_Daily_MB] = COALESCE(
					(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT) / 128.0) - (
						SELECT TOP 1 [Used_Space_MB]
						FROM [msdb].[dbo].[DBFileSizesLog]
						WHERE [DB_Name] = DB_NAME() AND [Logical_File_Name] = name
						ORDER BY [Log_Date] DESC
						),
					0
				)
			FROM [sys].[database_files]
			WHERE type IN (0, 1);
        ';

		-- Execute the dynamic SQL
		EXEC sp_executesql @sql;

		FETCH NEXT FROM db_cursor INTO @dbName;
	END;

	CLOSE db_cursor;
	DEALLOCATE db_cursor;
END;

---

3. SQL Server Agent Job 등록

이제 작성한 프로시저를 SQL Server Agent Job 으로서 주기적으로 수행하도록 만들자.

Agent Job 생성은 아래와 같이 SSMS에서 GUI로 하도록 하자.

[SQL Server 에이전트] - [작업] - 마우스 우클릭하여 [새 작업]

 

새로운 Agent Job 을 생성하는 창이 아래와 같이 나타났다면, 알아볼 수 있도록 적절한 Job 의 이름을 지어주도록 하자.

[새 작업] 창에서 [일반] 탭 입력

 

그리고 [단계] 에서 새로운 [작업]을 만들며, 여기에서도 해당 [단계] 를 알아보기 쉽도록 'Run Procedures' 와 같이 지어준다.

그리고 가장 중요한 실제 프로시저를 실행하는 쿼리를 아래와 같이 작성해 준다. 위에서 우리가 만든 프로시저 이름을 호출하여 수행해 주어야 한다.

EXEC [msdb].[dbo].[USP_Logging_DB_Sizes]

[새 작업] 창에서의 [단계] - [새로 만들기] - [일반] 창 작성

 

그리고 다음과 같이 [작업 일정]에 대한 이름, 해당 agent job 이 어느 빈도로, 매번 몇시에, 몇 번이나 반복할 지 등을 다음과 같이 설정해 준다.

[새 작업] 창의 [일정] - [새로 만들기] - [새 작업 일정] 창 작성

 

그리고 [새 작업] 창의 [확인]을 눌러주면, 당신이 지정한 Job 이름으로 새로운 SQL Server Agent Job 이 생성된 것을 확인할 수 있을 것이다.

 

Agent Job 생성을 완료했다면, 아래 그림처럼 반드시 해당 Job 을 한번 실행시켜 보도록 하자.

 

이렇게 Agent Job이 별다른 에러 로그 없이 잘 실행이 되었다면, DB 사이즈를 로깅하는 위 과정을 모두 잘 수행한 것이다.

그럼, 다음 날에 DB 사이즈가 얼마나 변화했는 지 확인해보고, DB 사이즈의 추이를 파악해 보도록 하자!