FCI (フェールオーバークラスターインスタンス) 環境の SQL Server では、デフォルトの設定で診断ログが取得されています。
グディレクトリに SQLDIAG ログが拡張イベントのファイルとして出力されており、問題が発生した場合にはこのファイルの内容の解析を行うことがあります。
スタンドアロン環境の SQL Server インスタンス (非 FCI の単一サーバー環境) では診断ログは出力されていないのですが、設定を行うことでスタンドアロンインスタンスでも取得を行うことができます。
検証環境には FCI も構築している環境はあるのですが、スタンドアロンインスタンスのほうが検証を実施しやすい内容があるため、本投稿ではスタンドアロンインスタンスで診断ログを取得する方法についてまとめておきたいと思います。
FCI の診断ログ (SQLDIAG ログ) の取得について
前述のとおり FCI ではデフォルトで診断ログの取得が行われており、次のドキュメントに記載されています。
診断ログに関連するドキュメントとしては、上記のドキュメントの他に次のドキュメントもあります。
- sp_server_diagnostics (Transact-SQL)
- sys.dm_os_server_diagnostics_log_configurations
- ALTER SERVER CONFIGURATION (Transact-SQL)
診断ログの設定は「sys.dm_os_server_diagnostics_log_configurations」で確認をすることができ、デフォルトでは「診断ログの取得: 有効 / 1 ファイルの最大サイズ: 100MB / 最大ファイル数: 10 ファイル」で設定が行われています。
この設定を変更するためには、ALTER SERVER CONFIGURATION を実行します。このステートメントは「DIAGNOSTICS LOG」というオプションがあり、このオプションの設定を変更することで、上述のサイズやファイル数等の設定を変更することが可能です。
診断ログの設定はこれらの設定により制御が行われていますが、診断ログの取得を行うためには、定期的な情報を取得する必要があり、この情報取得が sp_server_diagnostics により実行されています。
FCI 環境では、SQL Server のクラスターインスタンスに対してのクラスターのリソースホストサブシステム (rhs.exe) が、「EXEC sp_server_diagnostics 20」を実行しており、20 秒間隔で情報の取得が行われるような設定となっています。
これを拡張イベントセッションの「sp_server_diagnostics session」で拡張イベントとして出力することで、SQLDIAG 診断ログが出力されるのが基本的な動作となっているかと思います。
スタンドアロン環境のインスタンスで診断ログ (SQLDIAG ログ) の取得を行うためには
スタンドアロン環境の SQL Server インスタンスで診断ログを取得するためには次の 2 つの設定が必要となります。
- 拡張イベントで「sp_server_diagnostics_component_result」を取得するイベントを作成する
- sp_server_diagnostics を実行する
これらの設定を行うことで、スタンドアロン環境の SQL Server インスタンスでも SQLDIAG ログを取得することができるようになります。
拡張イベントについては、A. 拡張イベント セッションからの出力をファイルに保存する のクエリで設定を行うことができます。
FCI の設定に近づけた場合には次のようなクエリとなるのでしょうかね。
CREATE EVENT SESSION [diag] ON SERVER
ADD EVENT sqlserver.sp_server_diagnostics_component_result(SET collect_data=(1)
ACTION(sqlserver.session_id,sqlserver.sql_text))
ADD TARGET package0.event_file(SET filename=N'diag',max_file_size=(100),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
拡張イベントの設定をしても、情報の取得を行うことはできません。実際に情報を取得するためには、sp_server_diagnostics を実行しておく必要があります。
前述のとおり FCI では 20 秒間隔で取得を行うようにしてストアドが実行されていますので、同等の取得とする場合には次のクエリを実行しておきます。
EXEC sp_server_diagnostics 20
これにより、ストアドが実行されている間は拡張イベントにより SQLDIAG ログの取得が行われます。
このような方法を使用することでスタンドアロン環境の SQL Server インスタンスでも SQLDIAG ログを取得することができますので、SQLDIAG ログの内容を確認したいが直ぐに FCI が用意できない場合などは、このような方法を使用してみるとよいのではないでしょうか。