エラー 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プライマリ レプリカです。
  • トランザクション ログ ファイルの自動拡張オプションは、SQL Serverで設定されます。

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

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

原因

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

トラブルシューティング

可用性データベースと '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

    是正措置には、次のものが含まれますが、これに限定されるわけではありません。

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

回避策

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

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

    注:

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

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

    注:

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

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

  • トランザクション ログ ファイルに到達し、使用可能なディスク領域がある場合は、そのトランザクション ログ ファイルの 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