Azure SQL Database でのトランザクション ログ エラーのトラブルシューティング

  • [アーティクル]

適用対象:Azure SQL Database

トランザクション ログがいっぱいになり、新しいトランザクションを受け付けることができない場合、エラー 9002 または 40552 が表示されることがあります。 これらのエラーは、Azure SQL Database によって管理されているデータベースのトランザクション ログが領域のしきい値を超えており、トランザクションを引き続き受け入れることができない場合に発生します。 これらのエラーは、SQL Server の完全なトランザクション ログに関する問題と似ていますが、SQL Server、Azure SQL Database、Azure SQL Managed Instance で異なる解決策があります。

Note

この記事では、Azure SQL Database について重点的に説明します。 Azure SQL Database は、Microsoft SQL Server データベース エンジンの最新の安定バージョンに基づいているため、トラブルシューティングのオプションやツールは SQL Server と相違点はありますが、多くは類似しています。

Azure SQL Managed Instance でのトランザクション ログのトラブルシューティングの詳細については、「Azure SQL Managed Instance を使用したトランザクション ログ エラーのトラブルシューティング」を参照してください。

SQL Server でのトランザクションログのトラブルシューティングの詳細については、「完全なトランザクション ログのトラブルシューティング (SQL Server エラー 9002)」を参照してください。

自動バックアップとトランザクション ログ

Azure SQL Databaseでは、トランザクション ログのバックアップが自動的に作成されます。 詳細については、「自動バックアップ」に関する記事を参照してください。

ディスクの空き領域、データベース ファイルの拡張、ファイルの場所も管理されるため、トランザクション ログの問題の一般的な原因と解決策は SQL Server とは異なります。

SQL Server と同様に、ログのバックアップが正常に完了するたびに、各データベースのトランザクション ログが切り捨てられます。 切り捨てにより、ログ ファイルに空き領域ができて、新しいトランザクションに使用できるようになります。 ログ ファイルをログのバックアップで切り捨てることができない場合、ログ ファイルは新しいトランザクションを収容するために拡張されます。 ログ ファイルが Azure SQL Database の上限に達すると、新しい書き込みトランザクションは失敗します。

トランザクション ログのサイズについては、次のページを参照してください。

トランザクション ログの切り捨てを妨げているもの

特定のケースでログの切り捨てが妨げられている原因を調べるには、sys.databases の log_reuse_wait_desc を参照してください。 「ログ再利用の待機」によって、通常のログのバックアップによるトランザクション ログの切り捨てが妨げられている状況または原因が通知されます。 詳細については、「sys.databases (Transact-SQL)」を参照してください。

SELECT [name], log_reuse_wait_desc FROM sys.databases;

Azure SQL Database の場合、このクエリを実行するには、master データベースではなく、特定のユーザー データベースに接続することをお勧めします。

sys.databases の log_reuse_wait_desc の次の値によって、データベースのトランザクション ログの切り捨てが妨げられている理由が示されている場合があります。

log_reuse_wait_desc 診断 対応が必要
NOTHING 通常の状態。 ログの切り捨てをブロックしているものはありません。 いいえ。
CHECKPOINT ログの切り捨てを行うには、チェックポイントが必要です。 まれ。 継続しない限り、対応は必要ありません。 継続している場合は、Azure サポートにサポート要求を申請してください。
LOG BACKUP ログ バックアップが必要です。 継続しない限り、対応は必要ありません。 継続している場合は、Azure サポートにサポート要求を申請してください。
ACTIVE BACKUP OR RESTORE データベースのバックアップが実行されています。 継続しない限り、対応は必要ありません。 継続している場合は、Azure サポートにサポート要求を申請してください。
ACTIVE TRANSACTION 進行中のトランザクションにより、ログの切り捨てが妨げられています。 アクティブなトランザクションまたはコミットされていないトランザクションがあるため、ログ ファイルを切り捨てることができません。 次のセクションをご覧ください。
REPLICATION Azure SQL Database では、変更データ キャプチャ (CDC) が有効になっている場合に、これが発生する可能性があります。 sys.dm_cdc_errors に問い合わせてエラーを解決します。 解決できない場合は、Azure サポートにサポート要求を申請してください。
AVAILABILITY_REPLICA セカンダリ レプリカへの同期が進行中です。 継続しない限り、対応は必要ありません。 継続している場合は、Azure サポートにサポート要求を申請してください。

アクティブなトランザクションによってログの切り捨てが妨げられている

トランザクション ログで新しいトランザクションが受け入れられない場合の最も一般的なシナリオは、実行時間の長いトランザクションまたはブロックされているトランザクションです。

次のサンプル クエリを実行して、コミットされていないトランザクションまたはアクティブなトランザクションとそのプロパティを確認します。

  • sys.dm_tran_active_transactions からトランザクションのプロパティに関する情報が返されます。
  • sys.dm_exec_sessions からセッション接続情報が返されます。
  • sys.dm_exec_requests から要求情報 (アクティブな要求の場合) が返されます。 このクエリは、ブロックされているセッションを識別するためにも使用できます。request_blocked_by を確認してください。 詳細については、「ブロッキング情報の収集」を参照してください。
  • sys.dm_exec_sql_text または sys.dm_exec_input_buffer DMV を使用して、現在の要求のテキストまたは入力バッファーのテキストが返されます。 sys.dm_exec_sql_text の text フィールドによって返されたデータが NULL の場合、要求はアクティブではないが、未処理のトランザクションがあります。 その場合は、sys.dm_exec_input_buffer の event_info フィールドには、データベース エンジンに渡された最後のステートメントが含まれています。
SELECT [database_name] = db_name(s.database_id)
, tat.transaction_id, tat.transaction_begin_time, tst.session_id 
, session_open_transaction_count = tst.open_transaction_count
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime())
, input_buffer = ib.event_info
, request_text = CASE  WHEN r.statement_start_offset = 0 and r.statement_end_offset= 0 THEN left(est.text, 4000)
                       ELSE    SUBSTRING ( est.[text],    r.statement_start_offset/2 + 1, 
                                           CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text])) 
                                                ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1
                                           END  )  END
, request_status = r.status
, request_blocked_by = r.blocking_session_id
, transaction_state = CASE tat.transaction_state    
                     WHEN 0 THEN 'The transaction has not been completely initialized yet.'
                     WHEN 1 THEN 'The transaction has been initialized but has not started.'
                     WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
                     WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
                     WHEN 4 THEN 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'
                     WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
                     WHEN 6 THEN 'The transaction has been committed.'
                     WHEN 7 THEN 'The transaction is being rolled back.'
                     WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name
, azure_dtc_state    --Applies to: Azure SQL Database only
             =    CASE tat.dtc_state 
                 WHEN 1 THEN 'ACTIVE'
                 WHEN 2 THEN 'PREPARED'
                 WHEN 3 THEN 'COMMITTED'
                 WHEN 4 THEN 'ABORTED'
                 WHEN 5 THEN 'RECOVERED' END
, transaction_type = CASE tat.transaction_type    WHEN 1 THEN 'Read/write transaction'
                                             WHEN 2 THEN 'Read-only transaction'
                                             WHEN 3 THEN 'System transaction'
                                             WHEN 4 THEN 'Distributed transaction' END
, tst.is_user_transaction
, local_or_distributed = CASE tst.is_local WHEN 1 THEN 'Local transaction, not distributed' WHEN 0 THEN 'Distributed transaction or an enlisted bound session transaction.' END
, transaction_uow    --for distributed transactions. 
, s.login_time, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
, session_cpu_time = s.cpu_time, session_logical_reads = s.logical_reads, session_reads = s.reads, session_writes = s.writes
, observed = sysdatetimeoffset()
FROM sys.dm_tran_active_transactions AS tat 
INNER JOIN sys.dm_tran_session_transactions AS tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions AS s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests AS r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib 
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) AS est;

さらに領域を解放するためのファイル管理

Azure SQL Database エラスティック プールでトランザクション ログの切り捨てられない場合は、エラスティック プールの領域の解放がソリューションの一部である可能性があります。 ただし、トランザクション ログ ファイルの切り捨てがブロックされている根本的な状況を解決することが重要です。 場合によっては、一時的により多くのディスク領域を作成すると、実行時間の長いトランザクションが完了できるため、通常のトランザクション ログのバックアップでトランザクション ログ ファイルの切り捨てがブロックされる状況を取り除くことができます。 ただし、領域を解放しても、トランザクション ログが再び拡張されるまでの一時的な救済にしかならない場合があります。

データベースのファイル領域およびエラスティック プールの管理の詳細については、「Azure SQL Database でのデータベースのファイル領域の管理」を参照してください。

エラー 40552:トランザクション ログの使用領域が多すぎるため、セッションを終了しました

40552: The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

この問題を解決するには、次の方法を試してください。

  1. この問題は、挿入、更新、削除など、すべての DML 操作で発生する可能性があります。 不要な書き込みを避けるために、トランザクションを確認してください。 バッチ処理を実装したり、複数の小さなトランザクションに分割したりして、すぐに操作される行の数を減らしてみてください。 詳細については、「 バッチ処理を使用して SQL Database アプリケーションのパフォーマンスを強化する方法」を参照してください。
  2. この問題は、インデックスの再構築操作によって発生する可能性があります。 この問題を回避するには、次の式が真であることを確認してください: (テーブル内の影響を受ける行の数) に (更新されるフィールドの平均サイズ (バイト単位) + 80) を乗算した結果 < 2 ギガバイト (GB)。 大きなテーブルの場合は、パーティションを作成し、テーブルの一部のパーティションでのみインデックスのメンテナンスを実行することを検討してください。 詳細については、「 パーティション テーブルとパーティション インデックスの作成」を参照してください。
  3. bcp.exe ユーティリティまたは System.Data.SqlClient.SqlBulkCopy クラスを使用して一括挿入を実行する場合は、1 回のトランザクションでサーバーにコピーされる行数を -b batchsize オプションまたは BatchSize オプションで制限してください。 詳細については、「 bcp Utility」を参照してください。
  4. ALTER INDEX ステートメントでインデックスを再構築する場合は、SORT_IN_TEMPDB = ON、ONLINE = ON、RESUMABLE=ON オプションを使用します。 再開可能なインデックスでは、ログの切り捨てがさらに頻繁に行われます。 詳細については、「ALTER INDEX (Transact-SQL)」を参照してください。

Note

他のリソース ガバナンスのエラーの詳細については、「リソース ガバナンス エラー」を参照してください。

次のステップ