데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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

 

  • 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/

 




강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
2010 재해복구를 위한 SQL Server 역할 가져오기 jevida(강성욱) 2017.01.11 2283
2009 비관리자 계정에 Profiler 실행 권한 부여하기 jevida(강성욱) 2017.01.11 3125
2008 SQL Server Agent 공유 일정 생성하기 jevida(강성욱) 2017.01.11 2147
2007 인덱스 리빌드는 통계를 업데이트 할까? jevida(강성욱) 2017.01.11 2352
2006 인덱스 유지관리 작업과 SQL Server 쿼리 성능 jevida(강성욱) 2017.01.11 3314
2005 네트워크 드라이브에 데이터베이스 복원하기 jevida(강성욱) 2017.01.11 4087
» 확장 저장 프로시저를 활용한 논리디스크 용량 확인 jevida(강성욱) 2017.01.11 2460
2003 날짜 참조 테이블 만들기 jevida(강성욱) 2017.01.11 3039
2002 인덱스 상세 정보 확인 jevida(강성욱) 2017.01.11 3757
2001 DTC Transacntion 오버헤드 jevida(강성욱) 2017.01.11 1289
2000 대용량 로드를 위한 BULK INSERT 옵션 jevida(강성욱) 2017.01.11 5283
1999 SQL Server 2014 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1612
1998 SQL Server 2012 Contained Database jevida(강성욱) 2017.01.11 1001
1997 SQL Server 2008R2 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1421
1996 SQL Server 2005 Diagnostic Information Queries jevida(강성욱) 2017.01.11 1361
1995 601 Error, Could not continue scan with NOLOCK due to SQL Server data Movement jevida(강성욱) 2017.01.11 3724
1994 데이터베이스의 모든 인덱스 생성 삭제 스크립트 만들기 jevida(강성욱) 2017.01.11 1662
1993 SQL Server Spinlock 소개 jevida(강성욱) 2017.01.11 1586
1992 Ad-hoc 쿼리와 실행계획 jevida(강성욱) 2017.01.11 2574
1991 로그인 계정이 접근할 수 있는 데이터베이스 확인 jevida(강성욱) 2017.01.11 3017





XE Login