SQL Server AlwaysOn可用性グループのフェールオーバー履歴や原因を調査するためのログまとめ

こんにちは。今回はAlways On可用性グループのフェールオーバー履歴や原因を確認する方法についてちょっと調べたので備忘メモとして残しておきたいと思います。

それではまいります。

フェールオーバー履歴を確認する方法

結論、以下のログから確認できそうです。（他にもあるかもしれませんが、ネットを漁ってたどり着けた範囲で・・）

• SQL Serverエラーログ
• SQLDIAGログ
• Windowsイベントログ

SQL Serverエラーログについては以下に説明があります。

SQL Server エラー ログ (可用性グループ) – SQL Server Always On | Microsoft Docs

次の現象は、SQL Server エラー ログで確認する必要があります。

・可用性データベースにアクセスできない
・可用性グループの予期しないフェールオーバー
・予期しない解決状態の可用性グループ
・不確定な状態の可用性グループ

また、こちらでは、上記エラーログに加えてWindowsイベントログやSQLDIAG（AlwaysOn Health Diagnostic Log）の拡張イベントログも確認するよう紹介されていました。

SQL Serverの可用性グループがフェイルオーバーしたが、発生した原因が分からない（フェイルオーバートラブル）：SQL Serverトラブルシューティング（33）（1/2 ページ） – ＠IT (itmedia.co.jp)

これらを実際に見てみたいと思います。今回は、プライマリレプリカのSQL Serverサービスを停止させて意図的にフェールオーバーを発生させてみました。

＊今回は、それぞれのログで取得できている情報の詳細比較までは行っていません。各ログの概観の確認までとなっていますが、必要になったらまた随時Updateしたいと思います。

エラーログ

エラーログは規定だと以下のような場所に出ているかと思います。

中身はこんな感じ。レプリカの状態が変わっていく様子が記録されています。ただ、これだと、フェールオーバーが発生したことは分かるけど、SQL Serverサービスがシャットダウンされたから、といった理由までは分からないのかな・・・？（ログを見落としてるかも）

出力サンプル

2022-07-31 03:55:04.42 spid82      The state of the local availability replica in availability group 'AG1' has changed from 'SECONDARY_NORMAL' to 'RESOLVING_PENDING_FAILOVER'.  The state changed because of a user initiated failover.  For more information, see the SQL Server error log or cluster log.  If this is a Windows Server Failover Clustering (WSFC) availability group, you can also see the WSFC management console.
2022-07-31 03:55:05.09 spid72      Always On: The local replica of availability group 'AG1' is preparing to transition to the primary role. This is an informational message only. No user action is required.
2022-07-31 03:55:05.91 spid72      The state of the local availability replica in availability group 'AG1' has changed from 'RESOLVING_PENDING_FAILOVER' to 'RESOLVING_NORMAL'.  The state changed because the availability group is coming online.  For more information, see the SQL Server error log or cluster log.  If this is a Windows Server Failover Clustering (WSFC) availability group, you can also see the WSFC management console.
2022-07-31 03:55:05.91 spid25s     Always On Availability Groups connection with primary database terminated for secondary database 'MyDB1' on the availability replica 'sqlserver-1' with Replica ID: {d74c2262-5cd5-4889-98f5-603a1aebeb4d}. This is an informational message only. No user action is required.
2022-07-31 03:55:05.94 spid72      The state of the local availability replica in availability group 'AG1' has changed from 'RESOLVING_NORMAL' to 'PRIMARY_PENDING'.  The state changed because the availability group is coming online.  For more information, see the SQL Server error log or cluster log.  If this is a Windows Server Failover Clustering (WSFC) availability group, you can also see the WSFC management console.
2022-07-31 03:55:05.94 spid75s     The availability group database "MyDB1" is changing roles from "SECONDARY" to "RESOLVING" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.
2022-07-31 03:55:05.94 Server      Server is listening on [ 10.4.4.5 <ipv4> 6789].
2022-07-31 03:55:05.94 spid75s     State information for database 'MyDB1' - Hardened Lsn: '(37:2024:1)'    Commit LSN: '(37:1992:2)'    Commit Time: 'Jul 31 2022  2:17AM'
2022-07-31 03:55:05.94 Server      The Service Broker endpoint is in disabled or stopped state.
2022-07-31 03:55:05.94 Server      Started listening on listener network name 'dnnlsnr' (VNN or DISTRIBUTED_NETWORK_NAME). No user action is required.
2022-07-31 03:55:05.94 Server      The Service Broker endpoint is in disabled or stopped state.
2022-07-31 03:55:05.94 Server      The lease worker of availability group 'AG1' is now sleeping the excess lease time (163953 ms) supplied during online. This is an informational message only. No user action is required.
2022-07-31 03:55:06.00 Server      The state of the local availability replica in availability group 'AG1' has changed from 'PRIMARY_PENDING' to 'PRIMARY_NORMAL'.  The state changed because the local replica has completed processing Online command from Windows Server Failover Clustering (WSFC).  For more information, see the SQL Server error log or cluster log.  If this is a Windows Server Failover Clustering (WSFC) availability group, you can also see the WSFC management console.
2022-07-31 03:55:06.00 spid75s     The availability group database "MyDB1" is changing roles from "RESOLVING" to "PRIMARY" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.
2022-07-31 03:55:06.00 spid75s     State information for database 'MyDB1' - Hardened Lsn: '(37:2024:1)'    Commit LSN: '(37:1992:2)'    Commit Time: 'Jul 31 2022  2:17AM'
2022-07-31 03:55:06.01 Server      The Service Broker endpoint is in disabled or stopped state.
2022-07-31 03:55:06.02 spid75s     [DbMgrPartnerCommitPolicy::GetReplicaInfoFromAg] DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: D74C2262-5CD5-4889-98F5-603A1AEBEB4D:5:4
2022-07-31 03:55:06.04 spid75s     [DbMgrPartnerCommitPolicy::SetReplicaInfoInAg] DbMgrPartnerCommitPolicy::SetSyncAndRecoveryPoint: D74C2262-5CD5-4889-98F5-603A1AEBEB4D:5:1
2022-07-31 03:55:06.08 spid26s     0 transactions rolled back in database 'MyDB1' (5:0). This is an informational message only. No user action is required.
2022-07-31 03:55:06.08 spid26s     Parallel redo is shutdown for database 'MyDB1' with worker pool size [1].

拡張イベントログ（AlwaysOn Health Diagnostic Log）

続いて拡張イベントログ。たしかに、同時刻にErrorLogと併せて拡張イベントログも更新されている。

拡張イベントログはSSMSから確認できます。

見た感じ、先ほどのエラーログと同じ情報も出力されている模様。

Windowsイベントログ

WindowsのEvent Viewer＞Windows Logs > Applicationからも同様の情報が確認できました。

以上、AlwaysOn可用性グループのフェールオーバー発生履歴・原因を確認する方法の簡単なまとめでした。

少しでも参考になりましたら幸いです。

おしまい