Azure SQL Managed Instance を使用したトランザクション ログ エラーのトラブルシューティング

適用対象: Azure SQL Managed Instance

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

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

Azure SQL Managed Instance では、トランザクション ログのバックアップが自動的に作成されます。 詳細については、「自動バックアップ」に関する記事を参照してください。 SQL マネージド インスタンスで自動バックアップがいつ実行されたかを追跡するには、バックアップ アクティビティの監視に関する記事をご覧ください。

データベース ファイルの場所と名前を管理することはできませんが、管理者はデータベース ファイルとファイルの自動拡張の設定を管理できます。 トランザクション ログの問題の一般的な原因と解決策は、SQL Server と似ています。

SQL Server と同様に、ログのバックアップが正常に完了するたびに、各データベースのトランザクション ログが切り捨てられます。 ログの切り捨てにより、非アクティブな 仮想ログ ファイル (VLF) がトランザクション ログから削除され、ファイル内の領域が解放されますが、ディスク上のファイルのサイズは変更されません。 ログ ファイル内の空き領域は、新しいトランザクションに使用できます。 ログ ファイルをログのバックアップで切り捨てることができない場合、ログ ファイルは新しいトランザクションを収容するために拡張されます。 ログ ファイルが Azure SQL Managed Instance の上限に達すると、新しい書き込みトランザクションは失敗します。

Azure SQL Managed Instance では、コンピューティングとは別に、制限までアドオン ストレージを購入できます。 詳細については、「さらに領域を解放するためのファイル管理」を参照してください。

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

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

SELECT [name], log_reuse_wait_desc FROM sys.databases;

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

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

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

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

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

Azure SQL Managed Instance では、コンピューティングとは別に、制限までアドオン ストレージを購入できます。 たとえば、Azure portal で [コンピューティングとストレージ] ページにアクセスして、ストレージを (GB 単位) で増やします。 トランザクション ログのサイズ制限については、「Azure SQL Managed Instance のリソース制限の概要」を参照してください。 詳細については、「Azure SQL Database でファイル領域を管理する」を参照してください。

バックアップ ストレージがお使いの SQL Managed Instance ストレージ領域から差し引かれることはありません。 バックアップ ストレージはインスタンス ストレージ領域から独立しており、サイズは制限されていません。

エラー 9002: データベースのトランザクション ログがいっぱいです

9002: The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

エラー 9002 は、同じ理由で SQL Server と Azure SQL Managed Instance で発生します。

トランザクション ログが満杯になった状況によって、適切な対処法が異なる場合があります。

エラー 9002 を解決するには、次の方法を試してください。

• トランザクション ログは、切り捨てられず、使用可能なすべての領域が使い果たされた。
  • Azure SQL Managed Instance のトランザクション ログ バックアップは自動的に行われるので、トランザクション ログ アクティビティが切り捨てられないようにする必要があります。 不完全なレプリケーション、CDC、または可用性グループの同期が切り捨てを妨げている可能性があります。「妨げられたトランザクション ログの切り捨て」を参照してください。
• SQL managed instance の予約済ストレージ サイズが上限に達したので、トランザクション ログを書き込みできません。
  • リソースの上限まで領域を追加します。空き領域を増やすには、「より多くの領域を解放するためのファイル管理」を参照してください。
• トランザクション ログのサイズは、固定の最大値に設定されている、または自動拡張が無効になっているため拡張されません。
  • 「ALTER DATABASE (Transact-SQL) の File および Filegroup オプション」の MAXSIZE プロパティと FILEGROWTH プロパティに関する記事を参照してください。

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

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

エラー 9002 は Azure SQL Managed Instance のエラー 40552 よりも一般的ですが、両方が発生する場合があります。

エラー 40552 を解決するには、次の方法を試してください。

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

次のステップ

• ブロッキング問題の説明と解決策
• Azure SQL Database および Azure SQL Managed Instance の接続に関する問題とその他のエラーのトラブルシューティング
• Azure SQL Database および SQL Managed Instance の一時的な接続エラーをトラブルシューティングする