확장 저장 프로시저를 활용한 논리디스크 용량 확인

확장 저장 프로시저를 활용한 논리디스크 용량 확인

 

• 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

 

 

매일 프로시저를 호출하여 디스크의 정보를 보고받는다면 디스크 공간으로 발생하는 장애는 미연에 방지 할 수 있으리라 생각한다.

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3517/reviewing-logical-hard-drive-details-using-tsql-and-ole-automation-procedures/

 

 

2015-02-24 / 강성욱 / http://sqlmvp.kr

 

 

디스크 공간 확인, SQL Server, MSSQL, 논리 디스크 정보, disk free space, 데이터베이스 공간확인, SQL Data Size, SQL Log Size, 디스크 정보, xp_fixeddrives, 확장 저장 프로시저