SQL Server でトランザクション ログが予期せず大きくなったり、いっぱいになったりする

適用対象: SQL Server 2012 StandardSQL Server 2012 DeveloperSQL Server 2012 Enterprise

概要


トランザクション ログの展開は、次のいずれかの理由またはシナリオで発生する可能性があります。メモSQL Server 2005 以降のバージョンでは、sys.databases カタログ ビューのlog_reuse_waitとlog_reuse_wait_desc列を確認して、トランザクション ログ領域が再利用されない理由と、トランザクション ログを切り捨てることができない理由を確認できます。コミットされていないトランザクション明示的な COMMIT コマンドまたは ROLLBACK コマンドを発行しない場合、明示的なトランザクションはコミットされません。これは、アプリケーションが対応する ROLLBACK コマンドを指定せずに CANCEL コマンドまたは Transact-SQL KILL コマンドを発行する場合に最も頻繁に発生します。トランザクションの取り消しは発生しますが、ロールバックされません。したがって、中止されたトランザクションがまだ開いているため、SQL Server はこの後に発生するすべてのトランザクションを切り捨てることはできません。DBCC OPENTRAN Transact-SQL 参照を使用して、特定の時点でデータベースにアクティブなトランザクションがあることを確認できます。この特定のシナリオの詳細については、次のサポート技術情報番号をクリックしてください。
295108不完全なトランザクションは、多数のロックとケースブロッキングを保持する可能性があります
171224 Transact-SQL KILL コマンドの動作について
また、SQL Server オンライン ブックの「DBCC OPENTRAN」トピックを参照してください。コミットされていないトランザクションが発生する可能性があるシナリオ:
  • すべてのエラーがロールバックを引き起こすことを前提としたアプリケーション設計。
  • 名前付きトランザクションまたは特別に入れ子になった名前付きトランザクションにロールバックするときに SQL Server の動作を完全に考慮しないアプリケーション設計。内部名のトランザクションにロールバックしようとすると、次のエラー メッセージが表示されます。
    サーバー: Msg 6401、レベル 16、状態 1、回線 13 は InnerTran をロールバックできません。その名前のトランザクションまたはセーブポイントが見つかりませんでした。
    SQL Server はエラー メッセージを生成した後、次のステートメントに進みます。これは仕様です。詳細については、SQL Server オンライン ブックの「入れ子になったトランザクション」または「SQL Server 内」を参照してください。
    • ペンは 1 つのトランザクション ユニットのみをペンで使用します (別のプロセスが自分のトランザクションを呼び出す可能性を考慮してください)。
    • COMMIT、ROLLBACK、RETURN、または同様のコマンドまたはステートメントを発行する前に、@@TRANCOUNTを確認してください。
    • 別の@@TRANCOUNTが自分の@@TRANCOUNTを "入れ子にする" 可能性があることを前提にコードを記述し、エラーが発生したときに外部@@TRANCOUNTをロールバックするように計画します。
    • トランザクションのセーブポイントオプションとマークオプションを確認します。(これらはロックを解放しません!
    • 完全なテストを実行します。
  • トランザクション内でのユーザー操作を可能にするアプリケーション。これにより、トランザクションは長時間開いたままになり、未処理トランザクションを切り捨てることができず、開いているトランザクションの後に新しいトランザクションがログに追加されるため、ブロッキングとトランザクション ログの増加が発生します。
  • 開いているトランザクションがないことを確認するために@@TRANCOUNTをチェックしないアプリケーション。
  • SQL Server へのクライアント アプリケーション接続を通知せずに閉じるネットワークまたはその他のエラー。
  • 接続プール。ワーカー スレッドが作成されると、接続を処理していない場合、SQL Server はそれらを再利用します。ユーザー接続がトランザクションを開始し、トランザクションをコミットまたはロールバックする前に切断し、その後の接続が同じスレッドを再利用した場合、前のトランザクションは開いたままになります。この状況では、前のトランザクションからロックが開いたままになり、ログ内のコミットされたトランザクションの切り捨てができなくなります。この結果、ログ ファイルのサイズが大きくなります。接続プールの詳細については、次のサポート技術情報番号をクリックしてください。
    164221 ODBC アプリケーションで接続プールを有効にする方法
自動拡張オプションが Microsoft SQL Server 2005 および以降のバージョン (SQL Server 2000 および SQL Server 7.0) で設定されている場合、トランザクション ログ ファイルはログ ファイルあたり最大ログ ファイル サイズ 2 TB まで自動的に拡張できます。通常、トランザクション ログ ファイルのサイズは、チェックポイントまたはトランザクション ログのバックアップによってトリガーされるトランザクション ログの切り捨ての間に発生する可能性のあるトランザクションの最大数を保持できる場合に安定します。ただし、場合によっては、トランザクション ログが非常に大きくなり、領域が不足したり、いっぱいになったりすることがあります。通常、トランザクション ログ ファイルが使用可能なディスク領域を使い切り、拡張できなくなった場合に、次のエラー メッセージが表示されます。
エラー: 9002、重大度: 17、状態: 2 データベース '%.*ls' のログ ファイルがいっぱいです。
SQL Server 2005 を使用している場合は、次のようなエラー メッセージが表示されます。
エラー: 9002、重大度: 17、状態: 2データベース '%.*ls' のトランザクション ログがいっぱいです。ログ内の領域を再利用できない理由については、sys.databases のlog_reuse_wait_descの列を参照してください。
このエラー メッセージに加えて、トランザクション ログを拡張するための領域が不足しているため、SQL Server はデータベースを問題ありとしてマークすることがあります。この状況から回復する方法の詳細については、SQL Server オンライン ブックの「ディスク領域が不足しています」を参照してください。
  • 非常に大きなトランザクション ログ ファイル。
  • トランザクションが失敗し、ロールバックが開始される場合があります。
  • トランザクションの完了に時間がかかる場合があります。
  • パフォーマンスの問題が発生する可能性があります。
  • ブロッキングが発生する可能性があります。
  • データベースは AlwaysOn 可用性グループに参加しています。
非常に大規模なトランザクショントランザクション ログ ファイル内のログ レコードは、トランザクションごとに切り捨てられます。トランザクション スコープが大きい場合、そのトランザクションとその後に開始されたトランザクションは、完了しない限りトランザクション ログから削除されません。これにより、ログ ファイルが大きくなる可能性があります。トランザクションが十分に大きい場合、ログ ファイルが使用可能なディスク領域を使い取り、エラー 9002 などの "トランザクション ログがいっぱい" の種類のエラー メッセージが表示されることがあります。この種のエラー メッセージが表示された場合の処理の詳細については、この資料の「詳細」を参照してください。さらに、大きなトランザクションをロールバックするには、多くの時間と SQL Server のオーバーヘッドが必要です。操作: DBCC DBREINDEX およびインデックスの作成SQL Server 2000 の復旧モデルの変更により、完全復旧モードを使用して DBCC DBREINDEX を実行すると、SELECT INTO または BULK COPY を使用して "Trunc" を使用すると、同等の回復モードで SQL Server 7.0 と比較してトランザクション ログが大幅に拡張される場合があります。chkpt にログオンします。DBREINDEX 操作後のトランザクション ログのサイズが問題になる場合もありますが、この方法を使用するとログの復元パフォーマンスが向上します。 トランザクション ログ バックアップから復元する場合これについては、次のマイクロソフト サポート技術情報資料で説明します。
232196使用されるログ領域は、バックアップから復元した後に増加するように見える
一括ログ モードを使用するように SQL Server 2000 を設定し、一括コピーまたは SELECT INTO ステートメントを発行すると、変更されたすべてのエクステントがマークされ、トランザクション ログのバックアップ時にバックアップされます。これにより、一括操作を実行した後でもトランザクション ログをバックアップし、エラーから回復できますが、トランザクション ログのサイズが増加します。SQL Server 7.0 には、この機能は含まれていません。SQL Server 7.0 では、変更されたエクステントのみが記録されますが、実際のエクステントは記録されません。したがって、ログは SQL Server 2000 では、一括ログ モードの SQL Server 7.0 よりも大幅に多くの領域を使用しますが、フル モードの場合と同じくらい多くはありません。クライアント アプリケーションがすべての結果を処理しないSQL Server にクエリを発行し、結果をすぐに処理しない場合は、ロックを保持し、サーバーの同時実行性を低下させる可能性があります。たとえば、結果セットを設定するために 2 つのページの行を必要とするクエリを発行するとします。SQL Server は、クエリを解析、コンパイル、および実行します。つまり、クエリを満たすために必要な行を含む 2 つのページに共有ロックが追加されます。さらに、すべての行が 1 つの SQL Server TDS パケット (サーバーがクライアントと通信する方法) に適合するとは考えます。TDS パケットはいっぱいになり、クライアントに送信されます。最初のページのすべての行が TDS パケットに収まる場合、SQL Server はそのページの共有ロックを解放しますが、2 番目のページでは共有ロックを残します。次に、SQL Server はクライアントがより多くのデータを要求するのを待ちます (たとえば、DBNEXTROW/DBRESULTS、SQLNextRow/SQLResults、またはフェッチラスト/フェッチファーストを使用してこれを行うことができます)。つまり、クライアントが残りのデータを要求するまで、共有ロックは保持されます。2 番目のページからデータを要求する他のプロセスがブロックされる場合があります。 トランザクション ログが展開を完了する前にクエリがタイムアウトし、誤った 'ログがいっぱいです' エラー メッセージが表示されるこのような場合は、十分なディスク領域がありますが、"領域不足" エラー メッセージが表示されます。この状況は、SQL Server 7.0 および SQL Server 2000 では異なります。これには時間がかかり、クエリが停止されたり、タイムアウト期間を超えたりする場合があります。SQL Server 7.0 では、この状況でエラー 9002 が返されます。この問題は SQL Server 2000.In SQL Server 2000 には適用されませんが、データベースに対して自動圧縮オプションを有効にしている場合、トランザクション ログが自動的に拡張される時間は非常に小さくなります。ただし、自動圧縮機能が同時に実行されているため、拡張できません。これにより、エラー 9002 の誤ったインスタンスが発生することもあります。通常、トランザクション ログ ファイルの自動拡張は迅速に行われます。ただし、次の状況では、通常よりも時間がかかる場合があります。
  • 増加増分が小さすぎます。
  • サーバーはさまざまな理由で低速です。
  • ディスク ドライブの速度が十分ではありません。
レプリケートされていないトランザクションレプリケーションを使用している場合は、パブリッシャデータベースのトランザクション ログ サイズを拡張できます。レプリケートされるオブジェクトに影響を与えるトランザクションは、"レプリケーション用" としてマークされます。コミットされていないトランザクションなどのこれらのトランザクションは、チェックポイント後、またはログ リーダー タスクがトランザクションをディストリビューション データベースにコピーしてマークを解除するまで、トランザクション ログをバックアップした後に削除されません。ログ リーダー タスクに問題があるため、パブリッシャデータベースでこれらのトランザクションを読み取れなくなる場合は、レプリケートされていないトランザクションの数が増えるにつれて、トランザクション ログのサイズが拡大し続ける可能性があります。DBCC OPENTRAN Transact-SQL 参照を使用して、最も古いレプリケートされていないトランザクションを識別できます。レプリケートされていないトランザクションのトラブルシューティング方法の詳細については、SQL Server オンライン ブックの「sp_replcounters」および「sp_repldone」のトピックを参照してください。詳細については、次のサポート技術情報番号をクリックしてください。
306769 [FIX] スナップショットパブリッシュされたデータベースのトランザクション ログを切り捨てることはできません
240039修正: DBCC OPENTRAN はレプリケーション情報を報告しません。
198514 [FIX] 新しいサーバーに復元すると、トランザクションがログに残ります
トランザクション ログ レコードをセカンダリ データベースに適用する AlwaysOn 'AVAILABILITY_REPLICA'AlwaysOn 可用性グループが有効になっている SQL Server 2012 では、SQL エラー ログに次のメッセージが表示される場合があります。
エラー: 9002、重大度: 17、状態: 9. データベース '%.*ls' のトランザクション ログがいっぱいです AVAILABILITY_REPLICA。
log_reuse_waitAVAILABILITY_REPLICA、AlwaysOn 可用性グループのセカンダリ レプリカが、このデータベースのトランザクション ログ レコードを対応するセカンダリ データベースに適用していることを示します。可用性データベースのログの増加につながる可能性があるシナリオと、AVAILABILITY_REPLICAのlog_reuse_waitには、次の 2 つのシナリオがあります。遅延が発生すると、プライマリ レプリカのデータベースに記録された変更が切り捨てられないようにします。シナリオ 2: やり直し待ち時間セカンダリ データベース ログ ファイルに強化されると、専用の REDO スレッドがログ レコードを適用します。REDO 操作が生成されたトランザクション ログに追いつかない場合は、ログの増加につながる可能性があります。セカンダリ レプリカのやり直し操作が対応するセカンダリ データベースに適用する際に、セカンダリ レプリカのやり直し操作が遅れている場合、プライマリはトランザクション ログを切り捨てることができません。セカンダリが複数ある場合は、ログの切り捨てが遅れるセカンダリ データベースを特定するには、複数のセカンダリにまたがる sys.dm_hadr_database_replica_states 動的管理ビューの truncation_lsn列を比較します。AlwaysOn ダッシュボードおよび 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 を反映します。実際のログ シーケンス番号ではありません。
メモsys.dm_hadr_database_replica_states ビューの詳細については、次の TechNet web サイトを参照してくださいhttp://technet.microsoft.com/en-us/library/ff877972.aspx

高度な情報


任意のデータベースのトランザクション ログは、一連の仮想ログ ファイル (VLF) として管理されます。SQL Server は、ログ ファイルの合計サイズと、ログが拡張されたときに使用される拡張増分に基づいて、VLF ファイルのサイズを内部的に決定します。ログは常に VLF 全体の単位で拡張され、VLF 境界にのみ圧縮できます。VLF は、アクティブ、リカバリー可能、および再利用可能の 3 つの状態のいずれかに存在できます。
  • ACTIVE: ログのアクティブな部分は、アクティブな (コミットされていない) トランザクションを表す最小ログ シーケンス番号 (LSN) から始まります。ログのアクティブな部分は、最後に書き込まれた LSN で終了します。アクティブ・ログの任意の部分を含む VLF はアクティブ VLF と見なされます (物理ログ内の未使用スペースは VLF の一部ではありません)。
  • RECOVERABLE: 最も古いアクティブなトランザクションの前に来るログの部分は、リカバリのために一連のログ・バックアップを維持するためにのみ必要です。
  • 再利用可能: トランザクション ログのバックアップを保持していない場合、または既にログをバックアップしている場合、SQL Server は最も古いアクティブなトランザクションの前に VLF を再利用します。
SQL Server は、物理ログ ファイルの末尾に達すると、ファイルの先頭に CIRCLING BACK 操作を発行することによって、物理ファイル内のその領域の再利用を開始します。実際には、SQL Server は、回復またはバックアップの目的で不要になったログ ファイル内の領域をリサイクルします。ログ バックアップ シーケンスが維持されている場合は、ログ レコードをバックアップまたは切り捨てるまで、最小 LSN より前のログの一部を上書きできません。ログ バックアップを実行すると、SQL Server はファイルの先頭に戻ることができます。SQL Server が循環してログ ファイルの前のログ レコードの書き込みを開始すると、ログの再利用可能な部分は論理ログの末尾からログのアクティブな部分の間になります。詳細については、SQL Server オンライン ブックの「トランザクション ログの物理アーキテクチャ」を参照してください。さらに、「内部 SQL Server 7.0」(スークアップ、ロン)の190ページで、この図と議論を見ることができます。マイクロソフト SQL Server 7.0、マイクロソフト プレス、1999)、および 182 ページから 186 ページの "内部 SQL Server 2000" (デラニー、カレン) の内部。SQL Server 2000、マイクロソフト プレス、2000)の内部)。
SQL Server 2000 および SQL Server 7.0 データベースには、自動拡張と自動圧縮を行うオプションがあります。これらのオプションを使用すると、トランザクション ログを圧縮または拡張できます。これらのオプションがサーバーに与える影響の詳細については、次のサポート技術情報番号をクリックしてください。
315512 SQL Server での自動拡張と自動圧縮の構成に関する考慮事項
トランザクション ログ ファイルの切り捨ては、トランザクション ログ ファイルの圧縮とは異なります。SQL Server がトランザクション ログ ファイルを切り捨てると、そのファイルの内容 (コミットされたトランザクションなど) が削除されます。ただし、ディスク領域の観点からファイルのサイズを表示する場合 (Windows エクスプローラやdirコマンドを使用する場合など)、サイズは変更されません。ただし、.ldf ファイル内の領域は、新しいトランザクションで再利用できるようになりました。SQL Server がトランザクション ログ ファイルのサイズを縮小する場合にのみ、実際にはログ ファイルの物理サイズが変更されます。トランザクション ログを圧縮する方法の詳細については、次のサポート技術情報番号をクリックしてください。
256650 SQL Server 7.0 トランザクション ログを圧縮する方法
272318 DBCC シュリンクファイルを使用して SQL Server 2000 のトランザクション ログを圧縮する
SQL Server 6.5 トランザクション ログの使用方法の詳細については、次のサポート技術情報番号をクリックしてください。
110139 SQL トランザクション ログがいっぱいの原因

SQL Server 2005 およびそれ以降のバージョンで大量のログ領域を消費するクエリを検索する方法

SQL Server 2005 およびそれ以降のバージョンでは、sys.dm_tran_database_transactions 動的管理ビュー (DMV) を使用して、大量のログ領域を消費するクエリを検索できます。sys.dm_tran_database_transactions DMV の次の列が役立ちます。
  • database_transaction_log_bytes_used
  • database_transaction_log_bytes_used_system
  • database_transaction_log_bytes_reserved
  • database_transaction_log_bytes_reserved_system
  • database_transaction_log_record_count
sys.dm_exec_requests DMV のsql_handle列を照会して、大量のログ領域を消費する実際のステートメント テキストを取得できます。これを行うには、[transaction_id] 列の sys.dm_tran_database_transactions DMV と sys.dm_tran_session_transactions DMV を結合し、[session_id] 列に sys.dm_exec_requests を追加します。sys.dm_tran_database_transactions DMV の詳細については、sys.dm_tran_database_transactions (Transact-SQL)マイクロソフト開発ネットワーク (MSDN) web サイトを参照してください。sys.dm_tran_session_transactions DMV の詳細については、sys.dm_tran_session_transactions (Transact-SQL) MSDN web サイトを参照してください。dm_exec_requests DMV の詳細については、sys.dm_exec_requests (Transact-SQL) MSDN web サイトを参照してください。