趣旨
SQL Serverデータベースの空き容量を確認して、設定した閾値を下回った場合に、イベントログにエラーメッセージを書き出すスクリプトです。
作成した背景
OSディスクの空き容量の関係上、データファイルの自動拡張ができない環境において、データベースの空き容量が無くなる前に何らかの手段で検知する仕組みが必要となったため作成しました。
やり方は、色々あるかと思いますがこれが一番簡単な方法かと思います。
データベースの空き容量が閾値を下回った場合に、イベントログにエラーメッセージが出るので、後はそれをZabbix等の監視ツールで拾って貰えれば、事前に検知できます。
また、空き容量がどの位のペースで減っていっているのか、確認できるように、ログファイルに確認した時点の空き容量をデータ蓄積するようにしています。
それをエクセルに取り込んで、グラフ化すれば良いかと思います。
利用上の留意点
・SQL Server2016及びWindows Server2012で動作確認をしています
・それぞれの利用環境に応じて適宜修正をしてください
・「dbcheck.sql」の「where name like '%_Data%'」の箇所は、私の環境ではデータファイルの名前に「_Data」を含めているためこの様にしています。
データファイルのみ集計対象となるようにしてください
実行するスクリプト
①データベースの空き容量を確認するSQLファイル
dbcheck.sql
set nocount on
select
name,
(cast (size as bigint) * 8/1024)-(FILEPROPERTY(file_name(fileid),'SpaceUsed')*8192.0/power(1024,2)) as [freespace]
into #dbfreespace
from dbo.sysfiles
where name like '%_Data%'
select sum(freespace) as 空き容量合計
from #dbfreespace
drop table #dbfreespace
②上記のSQLファイルを実行させるバッチファイル
dbcheck.bat
@echo off
setlocal
rem 一時ログファイル名
set fileLog=freespace.log
rem 空き容量データの蓄積ログファイル名
set log_file=db_freespace_check_log.txt
rem sqlcmd接続情報
set sqlcmd=sqlcmd -S testdb -E -d test -W -h -1 -i
rem 実行するsqlファイル
set sqlfile=dbcheck.sql
rem アラート監視閾値をMB単位で指定する
set Threshold=10000
rem ログファイルに記載する日時の書式設定
set CUR_DATE=%date:~-10%
set CUR_TIME=%time:~0,8%
set CUR_TIME=%CUR_TIME: =0%
rem sqlcmdで接続を行う
%sqlcmd% ./%sqlFile% -o ./%fileLog% 2>&1
for /F "tokens=1 delims=." %%a in ( ./%fileLog% ) do set FREESPACE=%%a
if %FREESPACE% lss %Threshold% (
eventcreate /id 999 /l application /so db_FreeSpace_Check /t ERROR /d "データベースの空き容量がアラート監視閾値を下回りました。"
)
rem 空き容量データのログ出力蓄積(単位は「MB」)
echo %CUR_DATE% %CUR_TIME%,%FREESPACE%>> ./%log_file%
del .\%fileLog%
exit
空き容量監視バッチの定期実行
①上記のスクリプト２つ(sql・バッチファイル)を任意のフォルダに配置する
②タスクスケジューラに、任意のトリガー(30分間隔等)でタスク実行されるように登録する