SQL Server/SQL Server Tip

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

SungWookKang 2015. 7. 23. 10:33
반응형

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

 

  • 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, 확장 저장 프로시저

반응형