トランザクションログ肥大化の対処方法 (log_reuse_wait_desc : DATABASE_MIRRORING) [SQL Server]

SQL Server トランザクションログ (.ldf) の肥大化は様々な要因により発生しますが、今回は、トランザクションログファイル (.ldf) 肥大化の要因が、「log_reuse_wait_desc : DATABASE_MIRRORING」の場合の対処方法について紹介していきたいと思います。

本ブログ内容は、SQL Server 2019 をベースにしていますが、SQL Server 2019 以前のバージョンでも同様の方法で確認可能です。

データベースミラーリング機能は、将来のバージョンで削除されることが決定されている機能であるため、代わりに AlwayOn 可用性グループ機能を使用することを検討することをお勧めします。

SQL Server 2019 でデータベースミラーリングを構成する場合、SQL Server Management Studio (SSMS) の GUI からは構築できないため、コマンドから作成する必要があります。

log_reuse_wait_desc :「DATABASE_MIRRORING」は、データベースミラーリングを構成しているプリンシパル側のデータベースで実施されたトランザクション情報 (ログ情報)が、何らかの要因によりミラー側への反映ができない (遅延している) ことに起因し、プリンシパル側のデータベースのログレコードの切り捨てが行えない状態であることを示しています。

f:id:nobtak:20211231030726p:plain

そのため、

プリンシパル側のデータベースで実施されたトランザクション情報 (ログ情報) がミラー側へ反映できない (遅延している) 現象を対処
トランザクション情報 (ログ情報)の反映先であるミラー側をデータベースミラーリングの構成から切り離す

を実施することで、ログレコードの切り捨てが行える状態に改善します。

各々の対処方法について、紹介しようと思います。

ミラーリングが正常に動作していない現象を対処
ミラー側をデータベースミラーリングの構成から切り離す
まとめ
SQL Server トランザクションログ肥大化対策関連ブログ

ミラーリングが正常に動作していない現象を対処

ミラーリングが正常に動作していない状況時、プリンシパル側のSQL Server エラーログ (既定 : C:\Program Files\Microsoft SQL Server\MSSQL**.<インスタンス名>\MSSQL\Log\ERRORLOG) を確認し、どのようなエラーが発生しているかを確認します。

例えば、SQL Server エラーログ (ERRORLOG) を確認し、エラー 1474 :「Database mirroring connection error 2 'Connection attempt failed with error: '10061(対象のコンピューターによって拒否されたため、接続できませんでした。)」が出力されていた場合、プリンシパル側からミラー側へ接続ができないことを示しており、ミラー側のインスタンスが正しく起動しているか、ミラー側の SQL Server エラーログを確認し、何かミラー側でエラーが発生していないかなどを確認し、発生しているエラーを解消します。

[SQL Server エラーログ]

2021-**-** 02:56:04.94 spid64s Error: 1474, Severity: 16, State: 1.
2021-**-** 02:56:04.94 spid64s Database mirroring connection error 2 'Connection attempt failed with error: '10061(対象のコンピューターによって拒否されたため、接続できませんでした。)'.' for 'TCP://************:7022'.

ミラーリングが正常な状態に改善した場合、log_reuse_wait_desc :「DATABASE_MIRRORING」のステータスが「LOG_BACKUP」、「NOTHING」など変わり、ログレコードの切り捨てが行える状態に改善します。

f:id:nobtak:20211231032129p:plain

ミラー側をデータベースミラーリングの構成から切り離す

プリンシパル側ユーザーデータベースのトランザクションログが肥大化し、即座に対処が必要な状態、かつ、データベースミラーリングが正常に動作しない原因および対処に時間を要する場合、ミラー側をデータベースミラーリングの構成から切り離すことで、即座にプリンシパル側ユーザーデータベースのログレコードを切り捨て可能な状態に改善させることができます。

※ ミラー側をデータベースミラーリングの構成から切り離すと、データベースミラーリングの再構築が必要になる点に注意が必要。

以下に具体的な手順をまとめてみようと思います。

1) SQL Server Management Studio (SSMS) を起動し、管理者権限(sysadmin)が付与されたログイン(sa など)で、プリンシパル側の SQL Server インスタンスに接続します。

2) プリンシパルデータベース - 右クリック -「新しいクエリ」を選択します。

3) 以下のクエリを実行し、ミラー側のインスタンスをミラーリング構成から切り離します。

ALTER DATABASE <ミラーリング対象データベース名> SET PARTNER OFF
go

4) プリンシパルデータベースのステータスが、通常のユーザーデータベースの表示に戻ったことを確認します。

f:id:nobtak:20211231034728p:plain

[補足]

肥大化したトランザクションログを縮小する必要がある場合、以下のブログを参考に、トランザクションログの圧縮作業を実施します。

まとめ

今回は、トランザクションログファイル (.ldf) 肥大化の要因が、「log_reuse_wait_desc : DATABASE_MIRRORING」の場合の対処方法についてまとめてみました。

ミラー側をデータベースミラーリングの構成から切り離した場合、データベースミラーリングを再構築する必要があるため、プリンシパル側データベースのトランザクションログが肥大化し、即座に対処が必要になるなどの緊急を要する場合以外は、まずは、ミラーリングが正常に動作しない現象に関してエラー内容を確認し、ミラーリングが正常に動作する状態に改善するための調査を実施されるとよいかと思います。