xp_fixeddrives 프로시져를 이용한 디스크 여유공간 모니터링

서버관리자나, DBA는 주기적인 서버 모니터링을 통해서 서버가 항상 청청한 상태로 운영되도록 하기 위한 모니터링이 필요하다.

xp_fixeddrives 는 DBA를 위한 프로시져로 비공식 프로시져 이다. xp_fixeddrives 이미 여러 전문가들에 의해서 소개된바 있다.

SQL Server 에는 다음과 같은 비공식 프로시져들이 존재한다. (*여기서 비공식이란 온라인도움말 문서화에 포함되지 않는 프로시져를 말한다)

sp_MSgetversion
xp_dirtree
xp_enum_oledb_providers
xp_enumcodepages
xp_enumdsn
xp_enumerrorlogs
xp_enumgroups
xp_fileexist
xp_fixeddrives
xp_getnetname
xp_readerrorlog
xp_regdeletekey
xp_regdeletevalue
xp_regread
xp_regwrite
xp_subdirs

(예제: http://www.mssqlcity.com/Articles/Undoc/UndocExtSP.htm )


물론 디스크 여유공간 모니터링에 대한 방법은 사버 운영자의 운영기법에 따라서 여러가지가 존재할수 있다. 필자의 경우는 Windows 관리 인터페이스인 WMI를 권장한다.

참고 : WMI : 디스크 사용량 모니터링 Vbscript
http://www.serverinfo.pe.kr/TipnTech.aspx?Seq=221


실제 예제 쿼리를 보도록 하자. 아주 간단한 코드이므로 쉽게 이해할 수 있는 코드이다.

declare @MB_Free int

create table #FreeSpace(
 Drive char(1),
 MB_Free int)

insert into #FreeSpace exec xp_fixeddrives

select @MB_Free = MB_Free from #FreeSpace where Drive = 'C'

-- Free Space on C drive Less than Threshold
if @MB_Free < 1024
  exec master.dbo.xp_sendmail
     @recipients ='webmaster@serverinfo.pe.kr',
     @subject ='C Drive 여유공간 임계치 이하',
     @message = 'Free space on C Drive
                 has dropped below 1 gig'

select @MB_Free = MB_Free from #FreeSpace where Drive = 'F'

-- Free Space on F drive Less than Threshold
if @MB_Free < 2048
   exec master.dbo.xp_sendmail
     @recipients ='webmaster@serverinfo.pe.kr',
     @subject ='F 드라이브 여유공간 임계치 이하',
     @message = 'Free space on F Drive
                 has dropped below 2 gig'

쿼리 출처: http://www.databasejournal.com/features/mssql/article.php/3080501

위 쿼리는, 디스크 여유공간을 임시로 저장할 임시테이블 #FreeSpace 를 생성후  xp_fixeddrives 를 통해서 반환된 결과 레코드를 넣는 방법이다.

예제에서는, C 및 F 드라이브를 각각 모니터링하는데 지정된 용량 이하인 경우 xp_sendmail 프로시져를 통해서 메일을 보낸는 방법이다.

xp_sendmail 를 이용할려면, 사전에 SQL Mail 세선이 설정 및 시작되어 있어야 합니다. 그런데 이것은 실제 서버에서 AD 나 익스체인지가 가능해야 하므로 어렵습니다. 그래서 필자가 이전에 소개했던  SQL서버에서 sp_OACreate를 활용한 메일발송 예제 :
http://www.serverinfo.pe.kr/TipnTech.aspx?Seq=101 를 이용해 보는 것도 좋은 방법이다.

위 쿼리를 Sql Agent 스케쥴러에 등록을 해서 매일 또는 매주 실행하도록 해놓으면 좋은 모니터링 방법이 될것이다.


다음 웹페이지에 소개된 쿼리문도 참고해 보기 바란다. 위 쿼리문보다 훨씬더 자세한 정보를 기록하는 쿼리문으로 위의 경우 디스크 여유공간만 확인하는데 다음 쿼리는 전체 공간 및 GB 단위 변경코드도 추가되어 있다.

http://www.sqldbtools.com/Scripts/xp_FixedDrivesWrapper.sql
http://www.sql-server-performance.com/tp_centralized_sql_server_monitoring_b.asp