次の方法で共有

エラー 9002: SQL Server のAVAILABILITY_REPLICAエラー メッセージが原因で、データベースのトランザクション ログがいっぱいです

  • [アーティクル]

この記事は、トランザクション ログが大きくなったり、SQL Server の領域が不足したりしたときに発生する 9002 エラーを解決するのに役立ちます。

元の製品バージョン: SQL Server 2017、SQL Server 2016、SQL Server 2014、SQL Server 2012
元の KB 番号: 2922898

現象

以下のシナリオについて考えてみます。

  • Microsoft SQL Server 2012 以降のバージョンがサーバーにインストールされている。
  • SQL Server のインスタンスは、Always On 可用性グループ環境のプライマリ レプリカです。
  • トランザクション ログ ファイルの autogrow オプションは、SQL Server で設定されます。

このシナリオでは、トランザクション ログが大きくなり、ディスク領域が不足したり、プライマリ レプリカのトランザクション ログに設定された MaxSize オプションを超えたりして、次のようなエラー メッセージが表示されることがあります。

エラー: 9002、重大度: 17、状態: 9。 'AVAILABILITY_REPLICA' のため、データベース '%.*ls' のトランザクション ログがいっぱいです

原因

これは、プライマリ レプリカでログに記録された変更がまだセカンダリ レプリカで強化されていない場合に発生します。 Always On 環境でのデータ同期プロセスの詳細については、「データ同期hronization Process」を参照してください。

トラブルシューティング

可用性データベースと 'AVAILABILITY_REPLICA' log_reuse_wait_descでログの増加につながる可能性があるシナリオは 2 つあります。

  • シナリオ 1: ログに記録された変更をセカンダリに配信する待機時間

    トランザクションがプライマリ レプリカのデータを変更すると、これらの変更はログ レコード ブロックにカプセル化され、ログに記録されたブロックがセカンダリ レプリカのデータベース ログ ファイルに配信され、セキュリティが強化されます。 プライマリ レプリカは、ログ ブロックが配信され、すべてのセカンダリ レプリカの対応するデータベース ログ ファイルに書き込まれるまで、独自のログ ファイル内のログ ブロックを上書きできません。 可用性グループ内のレプリカへのこれらのブロックの配信またはセキュリティ強化に遅延が生じると、プライマリ レプリカのデータベースでログに記録された変更が切り捨てられなくなり、ログ ファイルの使用量が増加します。

    詳細については、「 ネットワーク待機時間が長いか、ネットワーク スループットが低い場合、プライマリ レプリカでログがビルドアップされるを参照してください。

  • シナリオ 2: やり直しの待機時間

    セカンダリ データベース ログ ファイルに対してセキュリティが強化されると、セカンダリ レプリカ インスタンスの専用再実行スレッドによって、含まれているログ レコードが対応するデータ ファイルに適用されます。 プライマリ レプリカは、すべてのセカンダリ レプリカのすべての再実行スレッドが含まれているログ レコードを適用するまで、独自のログ ファイル内のログ ブロックを上書きできません。

    セカンダリ レプリカでの再実行操作が、そのセカンダリ レプリカでログ ブロックが強化される速度に追いつくことができない場合、プライマリ レプリカでログが増加します。 プライマリ レプリカは、すべてのセカンダリ レプリカの再実行スレッドが適用された時点まで、独自のトランザクション ログのみを切り捨てて再利用できます。 複数のセカンダリがある場合は、複数のセカンダリでsys.dm_hadr_database_replica_states動的管理ビューのtruncation_lsn列を比較し、ログの切り捨てが最も遅れているセカンダリ データベースを特定します。

    Always On ダッシュボードと sys.dm_hadr_database_replica_states 動的管理ビューを使用して、ログ送信キューと再実行キューを監視できます。 いくつかの重要なフィールドは次のとおりです。

    フィールド 説明
    log_send_queue_size セカンダリ レプリカに到着していないログ レコードの量
    log_send_rate ログ レコードがセカンダリ データベースに送信される速度。
    redo_queue_size まだ再実行されていないセカンダリ レプリカのログ ファイル内のログ レコードの量 (KB 単位)。
    redo_rate 特定のセカンダリ データベースでログ レコードが再実行される速度 (KB/秒)。
    last_redone_lsn セカンダリ データベースで再実行された最後のログ レコードの実際のログ シーケンス番号。 last_redone_lsn は常に last_hardened_lsn未満です。
    last_received_lsn このセカンダリ データベースをホストするセカンダリ レプリカによってすべてのログ ブロックが受信されたポイントを識別するログ ブロック ID。 ゼロで埋め込まれたログ ブロック ID を反映します。 実際のログ シーケンス番号ではありません。

    たとえば、プライマリ レプリカに対して次のクエリを実行して、最も古い truncation_lsn でレプリカを報告します。これは、プライマリが独自のトランザクション ログで再利用できる上限です。

    SELECT ag.name AS [availability_group_name]
, d.name AS [database_name]
, ar.replica_server_name AS [replica_instance_name]
, drs.truncation_lsn , drs.log_send_queue_size
, drs.redo_queue_size
FROM sys.availability_groups ag
INNER JOIN sys.availability_replicas ar
    ON ar.group_id = ag.group_id
INNER JOIN sys.dm_hadr_database_replica_states drs
    ON drs.replica_id = ar.replica_id
INNER JOIN sys.databases d
    ON d.database_id = drs.database_id
WHERE drs.is_local=0
ORDER BY ag.name ASC, d.name ASC, drs.truncation_lsn ASC, ar.replica_server_name ASC

    是正措置には、次のものが含まれますが、これらに限定されません。

    • セカンダリにリソースやパフォーマンスのボトルネックがないことを確認します。
    • 再実行スレッドがセカンダリでブロックされていないことを確認します。 lock_redo_blocked拡張イベントを使用して、これがいつ発生し、再実行スレッドがブロックされているオブジェクトを識別します。

回避策

これを行うセカンダリ データベースを特定したら、次の 1 つ以上の方法を試して、この問題を一時的に回避してください。

  • 問題のあるセカンダリの可用性グループからデータベースを取り出します。

    Note

    この方法では、セカンダリの高可用性/ディザスター リカバリー シナリオが失われます。 今後、可用性グループをもう一度設定する必要がある場合があります。

  • 再実行スレッドが頻繁にブロックされる場合は、レプリカのSECONDARY_ROLEの ALLOW_CONNECTIONS パラメーターを NO に変更して、Readable Secondary機能を無効にします。

    Note

    これにより、ブロックの根本原因であるセカンダリ レプリカ内のデータをユーザーが読み取れなくなります。 再実行キューが許容可能なサイズに低下したら、この機能をもう一度有効にすることを検討してください。

  • 自動拡張設定が無効になっており、使用可能なディスク領域がある場合は有効にします。

  • トランザクション ログ ファイルに達し、使用可能なディスク領域がある場合は、そのファイルの MaxSize 値を増やします。

  • 現在のトランザクション ログ ファイルがシステムの最大 2 TB に達した場合、または別の使用可能なボリュームで追加の領域が使用可能な場合は、追加のトランザクション ログ ファイルを追加します。

詳細

適用対象

  • SQL Server 2012 Enterprise
  • SQL Server 2014 Enterprise
  • SQL Server 2014 Business Intelligence
  • SQL Server 2014 Standard
  • SQL Server 2016 Enterprise
  • SQL Server 2016 Standard
  • SQL Server 2017 Enterprise
  • SQL Server 2017 Standard Windows