SQL Server を実行しているコンピュータでトランザクション ログのサイズが予期せず増大する、または、ログがいっぱいになる

文書翻訳 文書翻訳
文書番号: 317375 - 対象製品
中小企業のお客様は、中小企業向けサポートサイトで問題解決コンテンツや学習リソースもご利用ください。
すべて展開する | すべて折りたたむ

目次

概要

SQL Server 7.0、SQL Server 2000、および SQL Server 2005 で、自動拡張の設定を使用すると、トランザクション ログ ファイルが自動的に拡張されます。

通常、チェックポイントまたはトランザクション ログのバックアップによって実行されるトランザクション ログの切り捨てから次回の切り捨てまでに発生する可能性のある最大数のトランザクションを保持できれば、トランザクション ログ ファイルのサイズは変化しません。

しかし、状況によっては、トランザクション ログが非常に大きくなり、トランザクション ログの領域が不足するか、トランザクション ログがいっぱいになることがあります。通常、利用可能なディスク領域がトランザクション ログ ファイルに占有され、それ以上拡張できなくなると、次のエラー メッセージが表示されます。
エラー : 9002、レベル : 17、状態 : 2
データベース '<database name>' のログ ファイルはいっぱいです。
SQL Server 2005 を使用している場合は、以下のようなエラー メッセージが表示されます。
エラー : 9002、レベル : 17、状態 : 2
データベース '%.*ls' のトランザクション ログがいっぱいです。ログの領域を再利用できない理由を確認するには、sys.databases の log_reuse_wait_desc 列を参照してください。
このエラー メッセージの他に、トランザクション ログを拡張するための領域が不足しているために、SQL Server でデータベースが "問題あり" と見なされることがあります。この状態から復旧する方法の関連情報については、SQL Server Books Online の「ディスク領域の不足」を参照してください。

また、トランザクション ログの拡張により、以下の現象が発生することがあります。
  • トランザクション ログ ファイルが非常に大きくなります。
  • トランザクションが失敗し、ロールバックが開始されることがあります。
  • トランザクションの完了までに長い時間がかかることがあります。
  • パフォーマンスの問題が発生することがあります。
  • ブロックが発生することがあります。

原因

トランザクション ログの拡張が発生する原因または状況を以下に示します。 : SQL Server 2005 では、sys.databases カタログ ビューの log_reuse_wait 列および log_reuse_wait_desc 列を参照すると以下の事項を確認できます。
  • トランザクション ログの領域が再利用されない理由
  • トランザクション ログが切り捨てられない理由

コミットされていないトランザクション

COMMIT コマンドまたは ROLLBACK コマンドを明示的に発行しないと、明示的なトランザクションはコミットされていない状態のままです。この現象が頻繁に発生するのは、アプリケーションで CANCEL コマンドまたは Transact SQL KILL コマンドを発行し、対応する ROLLBACK コマンドを発行しない場合です。トランザクションは取り消されますが、ロールバックが行われないため、中断されたトランザクションが開かれたままとなり、SQL Server でそれ以降の各トランザクションの切り捨てを行うことができません。DBCC OPENTRAN Transact-SQL リファレンスを使用すると、特定の時間にデータベース内にアクティブなトランザクションがあるかどうかを確認できます。 この特定の状況の関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
295108 [INF] 完了しないトランザクションが大量のロックを保持し、ブロッキングが発生
171224 [SQL]INF: Transact-SQL KILL コマンドの動作の理解
また、SQL Server Books Online の「DBCC OPENTRAN」を参照してください。

コミットされていないトランザクションが発生する状況
  • エラーの発生時に必ずロールバックされることを前提として、アプリケーションが設計されている場合。
  • アプリケーションの設計で、SQL Server が名前付きトランザクションまたは特別にネストされた名前付きトランザクションをロールバックする場合の SQL Server の動作が十分に考慮されていない場合。ネストの内側の名前付きトランザクションをロールバックしようとすると、次のエラー メッセージが表示されます。
    サーバー : メッセージ 6401、レベル 16、状態 1、行 13
    InnerTran をロールバックできません。見つかった名前のトランザクションまたは savepoint がありません。
    SQL Server はエラー メッセージを生成後、続けて次のステートメントを実行します。これは仕様です。詳細については、SQL Server Books Online の「トランザクションのネスト」または『Inside Microsoft SQL Server 2000』を参照してください。

    アプリケーションの設計に関する推奨事項を以下に示します。
    • トランザクション単位は 1 つのみ開くようにします (使用しているプロセスが、他のプロセスから呼び出される可能性を考慮します)。
    • COMMIT、ROLLBACK、RETURN などのコマンドやステートメントを発行する前に @@TRANCOUNT を確認します。
    • 他の @@TRANCOUNT によってネストになる可能性を考慮し、エラーが発生した場合、ネストの外側の @@TRANCOUNT をロールバックするように、コードを記述します。
    • トランザクションの savepoint とマーク オプションを確認します (この操作ではロックは解除されません)。
    • 十分なテストを行います。
  • トランザクション内部でのユーザーの操作を許可するアプリケーション。これにより、トランザクションが長時間開いたままになります。開かれているトランザクションは切り捨てることができず、開いたままのトランザクションの後に新しいトランザクションのログが追加されるため、ブロックが発生し、トランザクション ログが大きくなります。
  • アプリケーションで、開かれたままのトランザクションがないかどうかを @@TRANCOUNT で確認していない場合。
  • ネットワークやその他のエラーにより、SQL Server へのクライアント アプリケーションの接続が切断され、通知がない場合。
  • 接続プール。作成された後、接続によって使用されていないワーカー スレッドは、SQL Server により再利用されます。ユーザーの接続でトランザクションが開始され、トランザクションがコミットまたはロールバックされる前にその接続が切断され、その後の接続で同じスレッドが再利用されると、前のトランザクションが開かれたままになります。このため、前のトランザクションがロックされたままになり、ログ内のコミットされたトランザクションを切り捨てることができなくなります。その結果、ログ ファイルのサイズが大きくなります。 接続プールの関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
    164221 [INFO] ODBC アプリケーションで接続プールを有効にする方法

非常に大きいトランザクション

トランザクション ログ ファイル内のログ レコードは、トランザクション単位で切り捨てられます。トランザクションの範囲が大きい場合、そのトランザクションおよびそのトランザクション以降に開始されたトランザクションは、そのトランザクションが完了しない限り、トランザクション ログから削除されません。このため、ログ ファイルが大きくなることがあります。トランザクションが非常に大きい場合、利用可能なディスク領域がログ ファイルに消費され、エラー 9002 など、トランザクション ログがいっぱいであることを示すエラー メッセージが表示されます。このようなエラー メッセージが表示された場合の対処方法の関連情報については、この資料の「詳細」を参照してください。また、サイズが大きいトランザクションをロールバックするには、長い時間がかかり、SQL Server のオーバーヘッドも大きくなります。

操作 : DBCC DBREINDEX および CREATE INDEX

SQL Server 2000 における復旧モデルの変更により、完全復旧モードを使用して DBCC DBREINDEX を実行すると、SELECT INTO または BULK COPY を実行し、"チェックポイント時のログ切り捨て" を無効にした SQL Server 7.0 の同等の復旧モデルに比べて、トランザクション ログが大幅に増大することがあります。

DBREINDEX 操作後のトランザクション ログのサイズが問題になる場合がありますが、このアプローチはログ復元のパフォーマンスが優れています。

トランザクション ログのバックアップからの復元中

これについては、「サポート技術情報」 (Microsoft Knowledge Base) の次の資料に記載されています。
232196 [INF] バックアップから復元すると、ログに使用される領域が増加する

一括ログ記録モードを使用するように SQL Server 2000 を設定し、BULK COPY ステートメントまたは SELECT INTO ステートメントを発行すると、変更されたエクステントがすべてマークされ、トランザクション ログのバックアップ時にバックアップされます。これにより、一括操作を実行した後でも、トランザクション ログをバックアップし、エラーから復旧できますが、トランザクション ログのサイズが大きくなります。SQL Server 7.0 にはこの機能はありません。SQL Server 7.0 では、どのエクステントが変更されたのか、という情報は記録されますが、エクステント自体は記録されません。このため、SQL Server 2000 で一括ログ記録モードを使用すると、SQL Server 7.0 の場合よりも、ログ出力に使用される領域がはるかに大きくなります。ただし、使用される領域は、完全モードの場合ほどではありません。

クライアント アプリケーションで処理されない結果がある場合

SQL Server にクエリを発行し、その結果をすぐに処理しない場合、ロックが保持され、サーバー上の同時実行性が低下することがあります。

たとえば、発行されたクエリの結果セットを作成するために 2 つのページから行を取得する必要があるとします。このとき、要求されたすべての行を 1 つの SQL Server TDS パケットに格納できないと仮定します (TDS パケットは、サーバーとクライアントの通信に使用されます)。SQL Server はクエリを解析し、コンパイルし、実行します。つまり、クエリで要求されている行を含む 2 つのページに対して共有ロックが設定されます。その後、取得した情報が TDS パケットに格納され、クライアントに送信されます。このとき、1 ページ目のすべての行を 1 つの TDS パケットに格納できた場合、SQL Server はそのページの共有ロックを解除します。ただし、2 ページ目の共有ロックはそのままで、クライアントからの次のデータ要求を待ちます (ユーザーは、DBNEXTROW/DBRESULTS、SQLNextRow/SQLResults、FetchLast/FetchFirst などを使用してデータを要求できます)。

この場合、クライアントが残りのデータを要求しない限り、共有ロックは設定されたままです。2 ページ目のデータを要求するプロセスが他にあった場合、そのプロセスはブロックされます。

トランザクション ログの拡張が完了する前にクエリがタイムアウトし、エラー メッセージ "ログ ファイルがいっぱいです" が誤って表示される場合

この状況では、十分なディスク領域があるにもかかわらず、領域の不足を示すエラー メッセージが表示されます。

この状況は、SQL Server 7.0 と SQL Server 2000 では異なります。

トランザクション ログがほぼ上限に達している場合、クエリによりトランザクション ログが自動的に拡張されることがあります。この処理には、通常の処理より時間がかかる場合があります。このため、クエリが停止し、停止時間が長いためにクエリがタイムアウトする場合があります。この場合、SQL Server 7.0 ではエラー 9002 が返されます。この問題は、SQL Server 2000 では発生しません。

SQL Server 2000 では、データベースの "自動圧縮" オプションを有効にしている場合、トランザクション ログの自動拡張にかかる時間はきわめて短時間ですが、自動圧縮の機能が同時に実行されるため、拡張することができません。これにより、誤ってエラー 9002 という結果が返されることがあります。

通常、トランザクション ログ ファイルの自動拡張には時間はかかりません。ただし、以下の状況では、トランザクション ログ ファイルの自動拡張に通常より長い時間がかかります。
  • 拡張増分値が小さすぎます。
  • さまざまな理由でサーバーの処理速度が低下しています。
  • ディスク ドライブの処理速度が十分ではありません。

レプリケートされていないトランザクション

レプリケーションを使用している場合、パブリッシャ データベースのトランザクション ログのサイズが増加することがあります。コミットされていないトランザクションなど、レプリケート対象のオブジェクトに影響を与えるトランザクションは "レプリケーション用" としてマークされます。このようなトランザクションは、チェックポイントの後やトランザクション ログのバックアップ後も、ログ リーダー タスクによってディストリビューション データベースにコピーされ、マークが解除されるまで、削除されません。ログ リーダー タスクに問題が発生して、パブリッシャ データベース内のこれらのトランザクションから読み取りができない場合、レプリケートされていないトランザクション数の増加に伴って、トランザクション ログのサイズが大きくなることがあります。DBCC OPENTRAN Transact-SQL リファレンスを使用して、レプリケートされていないトランザクションのうち、最も古いものを特定することができます。

レプリケートされていないトランザクションのトラブルシューティングに関する詳細については、SQL Server Books Online の「sp_replcounters」および「sp_repldone」を参照してください。

関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
306769 [FIX] データベースをパブリッシュしたスナップショットのトランザクション ログを切り捨てられない
240039 [FIX] DBCC OPENTRAN でレプリケーション情報が報告されない
198514 [FIX] SQL Server 7.0 別サーバーへのリストアでトランザクションログがクリアされない

詳細

すべてのデータベースのトランザクション ログは、仮想ログ ファイル (VLF) のセットとして管理されます。VLF のサイズは、ログ ファイルの合計サイズとログ拡張時に使用している拡張増分値に基づいて SQL Server 内部で決定されます。ログは常に VLF 全体を単位にして拡張され、圧縮も VLF 単位でのみ行うことができます。VLF には ACTIVE、RECOVERABLE、および REUSABLE の 3 つの状態があります。
  • ACTIVE : ログのアクティブな部分の先頭には、最も数字の小さいログ シーケンス番号 (LSN) があります。LSN はアクティブな (コミットされていない) トランザクションを表します。ログのアクティブな部分の末尾には、最後に書き込まれた LSN が記録されています。ログのアクティブな部分を含んでいる VLF はすべて、アクティブな VLF と見なされます (物理ログの未使用の領域は、VLF には含まれません)。
  • RECOVERABLE : ログの最も古いアクティブなトランザクションより前の部分は、復旧のための、一連のログのバックアップを管理するためにのみ必要です。
  • REUSABLE : トランザクション ログのバックアップを保持していない場合、またはログを既にバックアップした場合、SQL Server では、最も古いアクティブなトランザクションより前の VLF が再利用されます。
SQL Server が物理ログ ファイルの終端に到達すると、SQL Server はファイルの先頭に戻って、物理ファイルの領域を再利用します。実際には、復旧またはバックアップを行うために必要ではなくなったログ ファイル内の領域が再利用されます。ログのバックアップ シーケンスが管理されている場合、ログの最も小さい LSN より前の部分は、そのログ レコードのバックアップまたは切り捨てが行われない限り、上書きできません。ログのバックアップが実行されると、SQL Server はファイルの先頭に戻ることができます。ファイルの先頭に戻って、ログ レコードの書き込みが始まると、論理的なログの最後とログのアクティブな部分との間の部分がログの再利用可能な部分になります。

関連情報については、SQL Server Books Online の「トランザクション ログの物理アーキテクチャ」を参照してください。また、『Inside Microsoft SQL Server 7.0』 (Ron Soukup 著、1999 年 Microsoft Press 発行) の 190 ページや、『Inside Microsoft SQL Server 2000』 (Kalen Delaney 著、2000 年 Microsoft Press 発行) の 182 〜 186 ページにもわかりやすい図と説明があります。 SQL Server 7.0 および SQL Server 2000 のデータベースには、自動拡張と自動圧縮を行うオプションがあります。これらのオプションを使用して、トランザクション ログの圧縮または拡張に役立てることができます。

これらのオプションがサーバーに与える影響の関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
315512 [INF] SQL Server における自動拡張および自動圧縮の構成に関する注意事項
トランザクション ログ ファイルの切り捨てと圧縮には違いがあります。SQL Server がトランザクション ログ ファイルを切り捨てる場合、そのファイルの内容 (コミットされたトランザクションなど) が削除されます。ディスク領域の点では、Windows エクスプローラ、または dir コマンドを使用してファイルのサイズを参照しても、ファイル サイズには変化はありません。しかし、.ldf ファイル内の領域は新しいトランザクションで再利用することができます。SQL Server によってトランザクション ログ ファイルのサイズが縮小された場合にのみ、ログ ファイルの物理サイズが実際に変更されます。

トランザクション ログを圧縮する方法の関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
256650 [INF] SQL Server 7.0 トランザクション ログを圧縮する方法
272318 INF: DBCC による SQL Server 2000 のログ圧縮
SQL Server 6.5 のトランザクション ログの使用方法の関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。
110139 INF: トランザクション ログがいっぱいになる原因

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 列を照会します。これを行うには、sys.dm_tran_database_transactions DMV と sys.dm_tran_session_transactions DMV を transaction_id 列で結合し、さらに sys.dm_exec_requests と session_id 列で結合します。

sys.dm_tran_database_transactions DMV の詳細については、次の MSDN (Microsoft Developer Network) Web サイトを参照してください。
http://msdn2.microsoft.com/ja-jp/library/ms186957.aspx
sys.dm_tran_session_transactions DMV の詳細については、次の MSDN Web サイトを参照してください。
http://msdn2.microsoft.com/ja-jp/library/ms188739.aspx
sys.dm_exec_requests DMV の詳細については、次の MSDN Web サイトを参照してください。
http://msdn2.microsoft.com/ja-jp/library/ms177648.aspx

プロパティ

文書番号: 317375 - 最終更新日: 2012年4月30日 - リビジョン: 8.0
この資料は以下の製品について記述したものです。
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
キーワード:?
kbsqlmanagementtools kbinfo KB317375
"Microsoft Knowledge Baseに含まれている情報は、いかなる保証もない現状ベースで提供されるものです。Microsoft Corporation及びその関連会社は、市場性および特定の目的への適合性を含めて、明示的にも黙示的にも、一切の保証をいたしません。さらに、Microsoft Corporation及びその関連会社は、本文書に含まれている情報の使用及び使用結果につき、正確性、真実性等、いかなる表明・保証も行ないません。Microsoft Corporation、その関連会社及びこれらの権限ある代理人による口頭または書面による一切の情報提供またはアドバイスは、保証を意味するものではなく、かつ上記免責条項の範囲を狭めるものではありません。Microsoft Corporation、その関連会社 及びこれらの者の供給者は、直接的、間接的、偶発的、結果的損害、逸失利益、懲罰的損害、または特別損害を含む全ての損害に対して、状況のいかんを問わず一切責任を負いません。(Microsoft Corporation、その関連会社 またはこれらの者の供給者がかかる損害の発生可能性を了知している場合を含みます。) 結果的損害または偶発的損害に対する責任の免除または制限を認めていない地域においては、上記制限が適用されない場合があります。なお、本文書においては、文書の体裁上の都合により製品名の表記において商標登録表示、その他の商標表示を省略している場合がありますので、予めご了解ください。"

フィードバック

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com