확장 저장 프로시저를 활용한 논리디스크 용량 확인
- Version : SQL Server 2005, 2008, 2008R2, 2012, 2014
DBA의 업무에서 중요한 작업 중 하나인 디스크 공간을 확인 하는 부분이다. 디스크의 총 용량, 사용 가능한 공간, 논리 드라이브의 정보 등을 확인하여 예기치 못한 장애에 대응할 수 있도록 해야 한다.
각 디스크 드라이브의 여유 공간을 확인하는 방법은 다양 하다. 윈도우 탐색기를 이용하여 파일 시스템 유형, 총 용량, 여유 가능한 공간 등을 확인 할 수 있다. 또 다른 방법으로는 확장 저장 프로시저 xp_fixeddrives을 사용하여 SQL Serve에서 직접 드라이브의 여유 공간을 확인 할 수 있다.
확장 저장 프로시저를 활용하여 논리 디스크의 정보를 확인하는 스크립트를 만들어 편하게 확인 할 수 있는 방법에 대해서 알아본다. 스크립트로 확인 할 수 있는 정보는 다음과 같다.
- 드라이브 문자
- 볼륨이름
- 파일 시스템 유형
- 전체 용량(GB)
- 남은 용량(GB)
- 남은 용량(%)
- 전체 데이터 파일 사이즈
- 전체 로그 파일 사이즈
드라이브의 속성을 확인하기 위해 sp_OACreate 및 sp_OAGetProperty를 사용하여 드라이브의 속성을 가져오는 함수를 생성한다.
USE [master] GO CREATE FUNCTION [dbo].[ufn_LogicalDiskDrives]() RETURNS @DriveList Table ( [DriveLetter] CHAR(1) ,[VolumeName] VARCHAR(255) ,[FileSystem] VARCHAR(50) ,[TotalSize] BIGINT ,[AvailableSpace] BIGINT ,[FreeSpace] BIGINT ) AS BEGIN --Written by Percy Reyes DECLARE @DriveLetter_ASCII_Code INT DECLARE @FileSystemInstance INT DECLARE @DriveCount INT DECLARE @DriveCollection INT DECLARE @Drive INT DECLARE @Property NVARCHAR(100) DECLARE @DriveLetter VARCHAR(1) DECLARE @TotalSize BIGINT DECLARE @AvailableSpace BIGINT DECLARE @FreeSpace BIGINT DECLARE @FileSystem VARCHAR(128) DECLARE @VolumeName VARCHAR(128) DECLARE @IsReady VARCHAR(5)
--Creating a File System Object for getting files or disk info. exec sp_OACreate 'Scripting.FileSystemObject', @FileSystemInstance OUT --Getting the collection of drives exec sp_OAGetProperty @FileSystemInstance,'Drives', @DriveCollection OUT --Getting the count of drives from collection exec sp_OAGetProperty @DriveCollection,'Count', @DriveCount OUT
--starting from Drive "A" (ASCII 65) SET @DriveLetter_ASCII_Code = 65 --to "Z" (ASCII 90) WHILE @DriveLetter_ASCII_Code <= 90 BEGIN ---Creating the instance drive from Drive Collection SET @Property = 'item("'+CHAR(@DriveLetter_ASCII_Code)+'")' exec sp_OAGetProperty @DriveCollection,@Property, @Drive OUT -- Getting the drive letter property exec sp_OAGetProperty @Drive,'DriveLetter', @DriveLetter OUT
IF @DriveLetter = CHAR(@DriveLetter_ASCII_Code) BEGIN -- Getting more properties from each drive exec sp_OAGetProperty @Drive,'VolumeName', @VolumeName OUT exec sp_OAGetProperty @Drive,'FileSystem', @FileSystem OUT exec sp_OAGetProperty @Drive,'TotalSize', @TotalSize OUT exec sp_OAGetProperty @Drive,'AvailableSpace', @AvailableSpace OUT exec sp_OAGetProperty @Drive,'FreeSpace', @FreeSpace OUT exec sp_OAGetProperty @Drive,'IsReady' , @IsReady OUT;
IF @IsReady='True' INSERT INTO @DriveList ( [DriveLetter],[TotalSize], [AvailableSpace],[FreeSpace],[FileSystem] ,[VolumeName] ) VALUES( @DriveLetter,@TotalSize,@AvailableSpace,@FreeSpace,@FileSystem,@VolumeName)
END -- forward next drive SET @DriveLetter_ASCII_Code = @DriveLetter_ASCII_Code +1 END
EXEC sp_OADestroy @Drive EXEC sp_OADestroy @DriveCollection
RETURN END |
위에서 생성한 함수가 정보를 가져 올 수 있도록 SQL Server 설정을 변경한다.
EXECUTE sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE GO EXECUTE sp_configure 'Ole Automation Procedures', 1 RECONFIGURE WITH OVERRIDE GO |
생성한 함수를 호출하여 정보를 확인한다. 논리 디스크의 볼륨명과 파일 시스템, 전체 용량과 남은 공간 등을 확인 할 수 있다.
SELECT * FROM [dbo].[ufn_LogicalDiskDrives]() |
아래 스크립트는 각 논리 드라이브에 위치한 데이터 파일 사이즈 및 로그파일 사이즈를 확인한다.
select ISNULL(DF.DriveLetter, LF.DriveLetter) DriveLetter, DF.DataSize_GB, LF.LogSize_GB from ( select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) DataSize_GB from sys.master_files where type_desc='ROWS' group by left(physical_name, 1), type_desc ) DF full outer join ( select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) LogSize_GB from sys.master_files where type_desc='LOG' group by left(physical_name, 1), type_desc ) LF on DF.DriveLetter=LF.DriveLetter |
아래 스크립트는 위에서 사용한 쿼리를 프로시저로 만들어 디스크의 정보를 확인 할 수 있도록 한다.
USE [master] GO CREATE PROC dbo.sp_fixeddrives WITH ENCRYPTION AS BEGIN SET NOCOUNT ON -----Reporting Drive details .... select LDD.DriveLetter ,[VolumeName] ,[FileSystem] ,CAST(TotalSize/1024.0/1024/1024.0 AS DECIMAL(10,2)) [Capacity_GB] ,CAST(FreeSpace/1024.0/1024/1024.0 AS DECIMAL(10,2)) [FreeSpace_GB] ,CAST((CAST(FreeSpace/1024.0/1024/1024.0 AS DECIMAL(10,2))/CAST(TotalSize/1024.0/1024/1024.0 AS DECIMAL(10,2)) ) *100 AS DECIMAL(10,2)) [Free %] , ISNULL( DBFiles.DataSize_GB,0) DataSize_GB,ISNULL( DBFiles.LogSize_GB,0) LogSize_GB from master.[dbo].[ufn_LogicalDiskDrives]() LDD full outer join ( select ISNULL(DF.DriveLetter, LF.DriveLetter) DriveLetter, DF.DataSize_GB, LF.LogSize_GB from ( select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) DataSize_GB from sys.master_files where type_desc='ROWS' group by left(physical_name, 1), type_desc ) DF full outer join ( select left(physical_name, 1) DriveLetter, CAST(sum(size)/128.0/1024 AS DECIMAL(10,2)) LogSize_GB from sys.master_files where type_desc='LOG' group by left(physical_name, 1), type_desc ) LF on DF.DriveLetter=LF.DriveLetter
) DBFiles ON DBFiles.DriveLetter =LDD.DriveLetter SET NOCOUNT OFF END |
생성한 프로시저 sp_fixeddrives를 어느 데이터베이스에서나 호출하여 사용 할 수 있도록 시스템 오브젝트로 등록 한다.
USE [master] GO EXEC sys.sp_MS_marksystemobject 'sp_fixeddrives' |
저장 프로시저를 실행하여 정보를 확인 한다.
EXEC sp_fixeddrives |
매일 프로시저를 호출하여 디스크의 정보를 보고받는다면 디스크 공간으로 발생하는 장애는 미연에 방지 할 수 있으리라 생각한다.
[참고자료]
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp
