SQL Server トランザクション ログのアーキテクチャと管理ガイド

適用対象: SQL Server (サポートされているすべてのバージョン) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

すべての SQL Server データベースにはトランザクション ログがあり、データベース内のすべてのトランザクションとそれらのトランザクションによって加えられた変更が記録されます。 トランザクション ログはデータベースの重要なコンポーネントであり、システム障害が発生した場合は、データベースを一貫した状態に戻すためにトランザクション ログが必要になる場合があります。 このガイドでは、トランザクション ログの物理アーキテクチャおよび論理アーキテクチャについて説明します。 アーキテクチャを理解することで、トランザクション ログを効率的に管理できるようになります。

トランザクション ログの論理アーキテクチャ

SQL Server のトランザクション ログは、論理的にはトランザクション ログが一続きのログ レコードから構成されているものとして機能します。 各ログ レコードは、 ログ シーケンス番号 (LSN) によって識別されます。 新しい各ログ レコードは、ログの論理上の末尾に前レコードの LSN より大きな LSN を付けて書き込まれます。 ログ レコードは、作成された順で連続して保管されます。LSN2 が LSN1 より大きい場合、LSN2 によって参照されるログ レコードで示される変更は、ログ レコード LSN1 で示される変更の後に行われます。 各ログ レコードにはトランザクション ID が含まれ、どのトランザクションについてのレコードかを示します。 各トランザクションに関連付けられているログ レコードはすべて、逆方向のポインターを使用して連鎖的にリンクされており、これによってトランザクションのロールバックをスピードアップできます。

データ変更のログ レコードは、実行された論理操作を記録するか、変更されたデータの前後のイメージを記録します。 before イメージは、操作が実行される前のデータのコピーです。after イメージは、操作が実行された後のデータのコピーです。

操作を復旧する手順は、ログ レコードの種類によって異なります。

  • 論理操作が記録されている場合

    • 論理操作をロールフォワードするために、操作が再度実行されます。
    • 論理操作をロールバックするには、逆論理操作が実行されます。
  • 前後イメージが記録されている場合

    • 操作をロールフォワードするには、後のイメージが適用されます。
    • 操作をロールバックするには、前のイメージが適用されます。

トランザクション ログには各種の操作が記録されます。 記録される操作には次のようなものがあります。

  • 各トランザクションの開始および終了。

  • あらゆるデータ変更 (挿入、更新、または削除)。 これには、システム ストアド プロシージャまたは DDL (データ定義言語) ステートメントによって、システム テーブルなどのテーブルに加えられた変更が含まれます。

  • エクステントおよびページのすべての割り当てと割り当て解除。

  • テーブルまたはインデックスの作成と削除。

ロールバック操作も記録されます。 各トランザクションは、明示的なロールバック ステートメントまたはエラーが発生した場合に発生したロールバックをサポートするのに十分なログ領域が存在することを確認するために、トランザクション ログ内の領域を予約します。 予約された領域の量は、トランザクションで実行された操作によって異なりますが、通常は各操作のログ記録に使用される領域の量と同じです。 この予約領域は、トランザクションが完了したときに解放されます。

ログ ファイルの中で、データベース全体を正常にロールバックするために必要な最初のログ レコードから、最後に書き込まれたログ レコードまでの部分を、ログのアクティブな部分、"アクティブ ログ"、または "ログの末尾" と呼びます。 これは、データベースの完全復旧を実行するために必要なログの部分です。 アクティブなログはどの部分も切り捨てることができません。 この最初のログ レコードのログ シーケンス番号 (LSN) は、 最小回復 LSN (MinLSN ) と呼ばれます。 トランザクション ログでサポートされている操作について詳しくは、「トランザクション ログ (SQL Server)」をご覧ください。

差分バックアップとログ バックアップの場合、復元されるデータベースは LSN が大きい方、つまり、より後の時点に向かって進められます。

トランザクション ログの物理アーキテクチャ

データベース トランザクション ログは、1 つ以上の物理ファイルにマップされます。 概念的には、ログ ファイルは一続きのログ レコードです。 物理的には、一連のログ レコードは、トランザクション ログを実装する一連の物理ファイルに効率的に格納されます。 1 つのデータベースにトランザクション ログ ファイルが少なくとも 1 つ必要です。

仮想ログ ファイル (VLF)

SQL Server データベース エンジンは、各物理ログ ファイルを内部的に複数の仮想ログ ファイル (VDF) に分割します。 仮想ログ ファイルのサイズは固定されておらず、物理ログ ファイルの仮想ログ ファイルの数も固定されません。 データベース エンジンは、ログ ファイルの作成または拡張中に、仮想ログ ファイルのサイズを動的に選択します。 データベース エンジンは、いくつかの仮想ファイルの保守を試みます。 ログ ファイルを拡張した後の仮想ファイルのサイズは、既存のログのサイズと増加した新しいファイルのサイズの合計になります。 仮想ログ ファイルのサイズまたは数は、管理者が構成または設定することはできません。

次の方法に従って仮想ログ ファイル (VLF) を作成します。

  • SQL Server 2014 (12.x) 以降のバージョンでは、次の拡張が現在のログ物理サイズの 1/8 未満の場合は、拡張サイズをカバーする 1 VLF を作成します。
  • 次の増加が現在のログ サイズの 1/8 を超える場合は、2014 より前の方法を使用します。
    • 拡張が 64 MB 未満の場合は、拡張サイズをカバーする 4 つの VDF を作成します (たとえば、1 MB の拡張の場合は、サイズ 256 KB の 4 つの VDF を作成します)。
      • Azure SQL Database では、SQL Server 2022 (16.x) (すべてのエディション) 以降では、これは若干異なります。 拡張が 64 MB 以下の場合、データベース エンジンは拡張サイズをカバーするために 1 つの VLF のみを作成します。
    • 拡張が 64 MB から 1 GB までの場合は、拡張サイズをカバーする 8 つの VDF を作成します (たとえば、512 MB の拡張の場合は、サイズ 64 MB の 8 VDF を作成します)。
    • 拡張が 1 GB を超える場合は、8 GB の拡張など、拡張サイズをカバーする 16 個の VDF を作成し、サイズ 512 MB の VDF を 16 個作成します。

ログ ファイルが多数の小さな増分で大きくなると、多くの仮想ログ ファイルが作成されます。 これにより、データベースの起動が遅くなり、バックアップと復元の操作がログに記録される可能性があります。 逆に、ログ ファイルが少数または 1 つの増分で大きなサイズに設定されている場合、非常に大きな仮想ログ ファイルはほとんどありません。 トランザクション ログの必要なサイズ自動拡張設定を適切に見積もる方法の詳細については、「トランザクション ログ ファイルのサイズを管理する」の「推奨事項」セクションを参照してください。

最適な VLF 分散を実現するために必要な増分を使用して、ログ ファイルに必要な最終的なサイズに近い サイズ 値を割り当て、比較的大きな growth_increment 値を設定することをお勧めします。

現在のトランザクション ログ サイズに対する最適な VLF 分布を判断するには、次のヒントを参照してください。

  • の引数ALTER DATABASEによって設定される SIZEsize 値は、ログ ファイルの初期サイズです。
  • の引数ALTER DATABASEによってFILEGROWTH設定されるgrowth_increment値 (自動拡張値とも呼ばれます) は、新しい領域が必要になるたびにファイルに追加される領域の量です。

の 引数と SIZE 引数のALTER DATABASE詳細についてはFILEGROWTH、「ALTER DATABASE (Transact-SQL) ファイルとファイル グループのオプション」を参照してください。

ヒント

特定のインスタンス内のすべてのデータベースの現在のトランザクション ログ サイズに対する最適な VLF 分布と、必要なサイズを達成するために必要な増加増分を判断するには、GitHub のこの修正 VLF スクリプト を参照してください。

VDF が多すぎるとどうなりますか?

データベース回復プロセスの初期段階では、SQL Serverはすべてのトランザクション ログ ファイルに存在するすべての VDF の検出を行い、これらの VDF の一覧を作成します。 このプロセスは、特定のデータベースに存在する VDF の数によっては、非常に長い時間がかかる場合があります。 VDF が多いほど、プロセスが長くなります。 トランザクション ログの自動拡張または手動の増加が頻繁に発生した場合、データベースは多数の VDF で終わる可能性があります。 VDF の数が数十万の範囲に達すると、次の症状の一部または大部分が発生する可能性があります。

  • 1 つ以上のデータベースは、SQL Serverの起動時に復旧を完了するのに非常に長い時間がかかります。
  • データベースの復元には、非常に長い時間がかかります。
  • データベースのアタッチを試みると、完了するまでに非常に長い時間がかかります。
  • データベース ミラーリングを設定しようとすると、タイムアウトを示すエラー メッセージ 1413、1443、1479 が発生します。
  • データベースを復元しようとすると、701 のようなメモリ関連のエラーが発生します。

SQL Server エラー ログを調べると、データベース回復プロセスの分析フェーズまでにかなりの時間が費やされていることがわかります。 次に例を示します。

2022-05-08 14:42:38.65 spid22s Starting up database 'lot_of_vlfs'.
2022-05-08 14:46:04.76 spid22s Analysis of database 'lot_of_vlfs' (16) is 0% complete (approximately 0 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.

さらに、多数の VDF を含むデータベースを復元すると、SQL Serverで 9017 エラーが記録されることがあります。

Database %ls has more than %d virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

詳細については、「 MSSQLSERVER_9017」を参照してください。

多数の VDF を使用してデータベースを修正する

VDF の合計数を妥当な量 (最大数千個など) に維持するには、次の手順を実行して、トランザクション ログ ファイルをリセットして、少数の VDF を含めることができます。

  1. トランザクション ログ ファイルを手動で圧縮します。

  2. 次の T-SQL スクリプトを使用して、1 つの手順でファイルを必要なサイズに手動で拡張します。

    ALTER DATABASE <database name> MODIFY FILE (NAME='Logical file name of transaction log', SIZE = <required size>);

    注意

    この手順は、データベースのプロパティ ページを使用して、SQL Server Management Studioでも可能です。

少数の VDF でトランザクション ログ ファイルの新しいレイアウトを設定した後、トランザクション ログの自動拡張設定を確認して必要な変更を行います。 これにより、ログ ファイルで今後も同じ問題が発生しないようにすることができます。

これらの操作のいずれかを実行する前に、後で問題が発生した場合に備えて、有効な復元可能なバックアップがあることを確認してください。

特定のインスタンス内のすべてのデータベースの現在のトランザクション ログ サイズに対する最適な VLF 分布と、必要なサイズを達成するために必要な増加増分を決定するには、次の GitHub スクリプトを使用して VLF を修正できます。

トランザクション ログの循環的な性質

トランザクション ログは、循環して使用されるファイルです。 たとえば、4 つの VLF に分割された 1 つの物理ログ ファイルが格納されたデータベースがあるとします。 このデータベースの作成時、論理ログ ファイルは物理ログ ファイルの先頭から始まります。 新しいログ レコードは論理ログの末尾に追加され、物理ログの末尾に向かって拡張されます。 ログの切り捨てにより、最小復旧ログ シーケンス番号 (MinLSN) より前にあるすべての仮想ログ レコードが解放されます。 MinLSN は、データベース全体を正常にロールバックするために必要な最も古いログ レコードのログ シーケンス番号です。 例として挙げたデータベースのトランザクション ログは、次の図のようになります。

物理ログ ファイルを仮想ログに分割する方法を示す図。

論理ログの末尾が物理ログ ファイルの末尾に達すると、新しいログ レコードはまた物理ログ ファイルの先頭から記録されていきます。

論理トランザクション ログが物理ログ ファイルでどのようにラップされるかを示す図。

このサイクルは、論理ログの末尾が論理ログの先頭に達しない限り、無限に繰り返されます。 古いログ レコードが頻繁に切り捨てられ、次のチェックポイントで作成されるすべての新規ログ レコードを格納するのに必要な領域が常に確保されている場合、論理ログがいっぱいになることはありません。 ただし、論理ログの末尾が論理ログの先頭に達した場合には、次のいずれかの処理が発生します。

  • ログに対して FILEGROWTH 設定が有効で、ディスク上の空き領域が使用可能な場合、ファイルは growth_increment パラメーターで指定された量だけ拡張され、新しいログ レコードが拡張機能に追加されます。 設定の FILEGROWTH 詳細については、「 ALTER DATABASE File and Filegroup Options (Transact-SQL)」を参照してください。

  • 設定が FILEGROWTH 有効になっていない場合、またはログ ファイルを保持しているディスクの空き領域が 、growth_incrementで指定された容量よりも少ない場合は、9002 エラーが生成されます。 詳細については、「満杯になったトランザクション ログのトラブルシューティング」を参照してください。

ログに複数の物理ログ ファイルが含まれている場合、論理ログは、すべての物理ログ ファイルの領域を使用し終えてから、最初の物理ログ ファイルの先頭に戻ります。

重要

トランザクション ログ サイズ管理の詳細については、「トランザクション ログ ファイルのサイズの管理」を参照してください。

ログの切り捨て

ログの切り捨ては、ログがいっぱいにならないようにするために不可欠です。 ログの切り捨てでは、 SQL Server データベースの論理トランザクション ログから非アクティブな仮想ログ ファイルが削除されます。これにより、論理ログの領域が解放され、物理トランザクション ログで再利用できるようになります。 トランザクション ログが切り捨てられなければ、最終的には、その物理ログ ファイルに割り当てられているすべてのディスク領域が満たされます。 ただし、ログの切り捨て前に、チェックポイント操作が必要です。 チェックポイントは、現在のメモリ内変更ページ ( ダーティ ページと呼ばれます) とトランザクション ログ情報をメモリからディスクに書き込みます。 チェックポイントが実行されると、トランザクション ログの非アクティブな部分は再利用できるようにマークが付けられます。 その後、ログの切り捨てにより、非アクティブな部分を解放できます。 チェックポイントの詳細については、「データベース チェックポイント (SQL Server)」を参照してください。

次の図は、切り捨てを行う前と後のトランザクション ログを示しています。 最初の図は、切り捨てが行われていないトランザクション ログを示しています。 現在、4 つの仮想ログ ファイルが論理ログで使用されています。 この論理ログは最初の仮想ログ ファイルの先頭から始まり、仮想ログ 4 で終了します。 MinLSN レコードは仮想ログ 3 にあります。 仮想ログ 1 および仮想ログ 2 には、非アクティブなログ レコードのみが含まれています。 これらのレコードは切り捨てることができます。 仮想ログ 5 はまだ使用されておらず、現在の論理ログの一部ではありません。

トランザクション ログが切り捨てられる前にどのように表示されるかを示す図。

2 番目の図は、切り捨て後のログの状態を示しています。 仮想ログ 1 および仮想ログ 2 は再利用のために解放されています。 この時点で、論理ログは仮想ログ 3 の先頭から始まっています。 仮想ログ 5 はまだ使用されておらず、現在の論理ログの一部ではありません。

トランザクション ログが切り捨てられた後にどのように表示されるかを示す図。

何かの理由で遅延が発生している場合を除いて、ログの切り捨ては、次のイベントの後に自動的に発生します。

  • 単純復旧モデルでは、チェックポイント以降。
  • 完全復旧モデルまたは一括ログ復旧モデルで、前回のバックアップ後にチェックポイントが発生している場合は、ログ バックアップの後。

ログの切り捨ては、さまざまな要因によって遅れる可能性があります。 ログの切り捨てで長時間の遅延が発生すると、トランザクション ログがいっぱいになる可能性があります。 詳細については、「ログの切り捨てを遅らせる要因」および「完全なトランザクション ログのトラブルシューティング (SQL Server エラー 9002)」を参照してください。

先書きトランザクション ログ

このセクションでは、データの変更をディスクに記録するときの先行書き込みトランザクション ログの役割について説明します。 SQL Server では、先行書き込みログ (WAL) アルゴリズムを使用します。これにより、関連付けられているログ レコードより前にデータ変更がディスクに書き込まれることがなくなります。 これにより、トランザクションの ACID プロパティが維持されます。

先書きログのしくみを理解するには、変更されたデータがディスクに書き込まれる方法を理解することが重要です。 SQL Serverは、データを取得する必要があるときにデータ ページを読み取るバッファー キャッシュ (バッファー プールとも呼ばれます) を維持します。 ページがバッファー キャッシュで変更されると、すぐにディスクに書き戻されることはありません。代わりに、ページは ダーティとしてマークされます。 データ ページは、ディスクに物理的に書き込まれる前に、複数の論理書き込みを行うことができます。 論理書き込みを行うたびに、トランザクション ログ レコードが、変更を記録するログ キャッシュに挿入されます。 ログ レコードは、関連付けられているダーティ ページがバッファー キャッシュから削除されディスクに書き込まれる前に、ディスクに書き込まれる必要があります。 チェックポイント プロセスでは、指定されたデータベースからのページを含むバッファーのバッファー キャッシュを定期的にスキャンし、ダーティ ページをすべてディスクに書き込みます。 チェックポイントは、すべてのダーティ ページがディスクに書き込まれたことを確認するために作成されるポイントで、その後の復旧の時間を短縮します。

変更されたデータ ページをバッファー キャッシュからディスクに書き込むことを "ページのフラッシュ" といいます。 SQL Server には、関連付けられているログ レコードの書き込み前にダーティ ページをフラッシュしないためのロジックが用意されています。 ログ レコードは、ログ バッファーがフラッシュされるときにディスクに書き込まれます。 これは、トランザクションがコミットされたとき、またはログ バッファーがいっぱいになったときに必ず発生します。

トランザクション ログのバックアップ

このセクションでは、トランザクション ログのバックアップと復元 (適用) の方法について説明します。 完全復旧モデルと一括ログ復旧モデルでは、データを復旧するためにトランザクション ログを定期的にバックアップすること (ログのバックアップ) が必要不可欠です。 ログのバックアップは、完全バックアップの実行中でも行うことができます。 復旧モデルの詳細については、「 SQL Server データベースのバックアップと復元」を参照してください。

最初のログ バックアップを作成する前に、データベース バックアップや一連のファイル バックアップの最初のバックアップを行って、完全バックアップを作成する必要があります。 ファイル バックアップだけを使ったデータベースの復元は複雑になる可能性があります。 したがって、可能な時点でデータベースの完全バックアップを行うことから始めることをお勧めします。 その後、トランザクション ログを定期的にバックアップする必要があります。 その結果、作業損失の可能性が最小限に抑えられるだけでなく、トランザクション ログの切り捨ても可能になります。 一般に、トランザクション ログは、通常のログ バックアップ後に毎回切り捨てられます。

重要

ログ バックアップは、ビジネス要件に対応するために十分な頻度で作成することをお勧めします。特に、ログ ストレージに障害が起こった場合に生じる作業損失に対する許容範囲を考慮してください。

ログ バックアップを行う適切な頻度は、作業損失に対する許容範囲と、ログ バックアップを保存、管理、復元できる量とのバランスによります。 復旧戦略を実装する際に必要な目標復旧時間 (RTO) と目標復旧ポイント (RPO) について考え、具体的にはログ バックアップ周期について考えてください。 15 分から 30 分間隔でログ バックアップを行えば十分でしょう。 業務上、作業損失の可能性を最小限に抑えることが求められる場合は、ログ バックアップの頻度を増やすことを検討します。 ログ バックアップの頻度を増やせば、ログ切り捨ての頻度も高くなり、ログ ファイルが小さくなる利点もあります。

復元する必要があるログ バックアップの数を制限するには、データを定期的にバックアップすることが不可欠です。 たとえば、データベースの完全バックアップを毎週実行し、差分バックアップを毎日実行するようにスケジュールできます。

復旧戦略を実装する際に必要な RTORPO 、特にデータベースの完全バックアップと差分バックアップの頻度について考えてください。

トランザクション ログ バックアップの詳細については、「トランザクション ログ バックアップ (SQL Server)」を参照してください。

ログ チェーン

ログ バックアップの連続的なシーケンスは、 ログ チェーンと呼ばれます。 ログ チェーンは、データベースの完全バックアップから始まります。 通常、新しいログ チェーンは、データベースが初めてバックアップされたとき、または復旧モデルが単純復旧から完全復旧または一括ログ復旧に切り替えられた後にのみ開始されます。 データベースの完全バックアップの作成時に既存のバックアップ セットを上書きしない限り、既存のログ チェーンはそのまま残ります。 ログ チェーンがそのまま残っている場合は、メディア セット内にあるデータベースの完全バックアップからデータベースを復元し、その後で復旧ポイントに達するまで後続のログ バックアップをすべて復元できます。 復旧ポイントは、最後のログ バックアップの末尾、または任意のログ バックアップの特定の復旧ポイントである場合があります。 詳細については、「トランザクション ログ バックアップ (SQL Server)」を参照してください。

データベースを障害の発生時点まで復元するには、ログ チェーンが途切れていないことが条件になります。 つまり、トランザクション ログ バックアップのシーケンスは、障害の発生時点まで途切れずに続いている必要があります。 この一連のログを開始する必要がある場所は、復元するデータ バックアップの種類 (データベース、部分、またはファイル) によって異なります。 データベース バックアップまたは部分バックアップの場合、ログ バックアップのシーケンスはデータベース バックアップまたは部分バックアップの最後から始まっている必要があります。 一連のファイル バックアップの場合、ログ バックアップのシーケンスはファイル バックアップの完全なセットから始まっている必要があります。 詳細については、「トランザクション ログ バックアップの適用 (SQL Server)」を参照してください。

ログ バックアップを復元する

ログ バックアップを復元すると、トランザクション ログに記録された変更がロールフォワードされ、ログ バックアップ操作の開始時にデータベースの正確な状態が再作成されます。 データベースを復元する場合は、復元するデータベースの完全バックアップの後、または復元する最初のファイル バックアップの開始後に作成されたログ バックアップを復元する必要があります。 通常、最新のデータまたは差分バックアップを復元した後、復旧ポイントに到達するまで一連のログ バックアップを復元する必要があります。 その後、データベースを復旧します。 その結果、復旧を開始したときに不完全だったトランザクションがすべてロールバックされ、データベースがオンラインになります。 データベースが復旧された後は、それ以上バックアップを復元することはできません。 詳細については、「トランザクション ログ バックアップの適用 (SQL Server)」を参照してください。

チェックポイントとログのアクティブな部分

チェックポイントにより、現在のデータベースのバッファー キャッシュのダーティ データ ページは、ディスクにフラッシュされます。 チェックポイントを使用すると、データベースの完全復旧時に処理する必要があるログのアクティブな部分を最小限に抑えることができます。 完全復旧時には、次の種類の操作が行われます。

  • システムが停止する前にディスクにフラッシュされていなかった変更のログ レコードがロールフォワードされます。
  • COMMIT または ROLLBACK ログ レコードがないトランザクションなど、不完全なトランザクションに関連付けられているすべての変更がロールバックされます。

チェックポイント操作

チェックポイントにより、データベースで次の処理が実行されます。

  • チェックポイントの開始位置を示すレコードをログ ファイルに書き込みます。

  • チェックポイント用に記録された情報をチェックポイント ログ レコードのチェーンに格納します。

    チェックポイントで記録される情報の 1 つは、データベース全体を正常にロールバックするために必要な最初のログ レコードのログ シーケンス番号 (LSN) です。 この LSN は、最小復旧 LSN (MinLSN) といいます。 MinLSN は、次の LSN の最小値です。

    • チェックポイントの開始の LSN
    • 最も古いアクティブなトランザクションの開始の LSN
    • ディストリビューション データベースにまだ配信されていない最も古いレプリケーション トランザクションの開始の LSN。

    チェックポイント レコードには、データベースに変更を加えたすべてのアクティブなトランザクションの一覧も含まれます。

  • データベースで単純復旧モデルが使用されている場合、MinLSN より前にある領域を再利用するようにマークを付けます。

  • すべてのダーティ ログとダーティ データ ページをディスクに書き込みます。

  • チェックポイントの終了位置を示すレコードをログ ファイルに書き込みます。

  • このチェーンの開始の LSN をデータベース ブート ページに書き込みます。

チェックポイントを発生させるアクティビティ

チェックポイントは次の状況で作成されます。

  • CHECKPOINT ステートメントが明示的に実行された場合。 接続を確立するために、現在のデータベースでチェックポイントが作成されます。
  • データベースで最小ログ記録操作が実行された場合。たとえば、一括ログ復旧モデルを使用しているデータベースで一括コピー操作が実行された場合です。
  • ALTER DATABASE を使用して、データベース ファイルが追加または削除された場合。
  • SHUTDOWN ステートメントを使用するか、SQL Server (MSSQLSERVER) サービスを停止して、SQL Server のインスタンスが停止された場合。 どちらの場合でも、SQL Server のインスタンスの各データベースにチェックポイントが作成されます。
  • データベースの復旧にかかる時間を短縮するために、SQL Server のインスタンスにより、各データベースで定期的に自動チェックポイントが作成されている場合。
  • データベースのバックアップが作成された場合。
  • データベースのシャットダウンが必要な動作が実行された場合。 たとえば、AUTO_CLOSE が ON に設定されていて、データベースへの最後のユーザー接続が終了した場合、またはデータベースの再起動が必要なデータベース オプションが変更された場合です。

自動チェックポイント

SQL Server データベース エンジンでは自動チェックポイントが生成されます。 自動チェックポイントが作成される間隔は、使用されているログ領域の量と最後のチェックポイントが作成されてからの経過時間に基づいています。 データベースにほとんど変更が加えられない場合、自動チェックポイントが作成される間隔は変化して長くなります。 また、多くのデータが変更される場合、自動チェックポイントは頻繁に作成されます。

[復旧間隔] サーバー構成オプションを使用して、サーバー インスタンス上のすべてのデータベースの自動チェックポイントの間隔を計算します。 このオプションでは、システムの再起動時にデータベース エンジンでデータベースの復旧に使用できる最長時間を指定します。 データベース エンジンでは、復旧操作時に [復旧間隔] オプションで指定された時間内に処理できる推定ログ レコード数が算出されます。

自動チェックポイントの作成間隔は、復旧モデルによっても異なります。

  • データベースで完全復旧モデルまたは一括ログ復旧モデルを使用している場合、[復旧間隔] オプションで指定された時間内に処理できるとデータベース エンジンが算出したログ レコード数に達するたびに、自動チェックポイントが作成されます。

  • データベースで単純復旧モデルを使用している場合、ログ レコード数が次の 2 つの値のうち、小さい方の値に達するたびに自動チェックポイントが作成されます。

    • ログが全体の 70% に達したとき。
    • [復旧間隔] オプションで指定された時間内に処理できるとデータベース エンジンが推定したログ レコード数に達したとき。

復旧間隔の設定については、「 recovery interval サーバー構成オプションの構成」を参照してください。

ヒント

一部の種類のチェックポイントでは、データベース管理者が -k SQL Server の詳細設定オプションを使用して、I/O サブシステムのスループットに基づいてチェックポイントの I/O 動作を調整できます。 セットアップ オプションは -k 、自動チェックポイントと、それ以外の場合は非rottled チェックポイントに適用されます。

データベースで単純復旧モデルを使用している場合、自動チェックポイントにより、トランザクション ログの未使用のセクションが切り捨てられます。 ただし、データベースが完全復旧モデルまたは一括ログ復旧モデルを使用している場合、ログは自動チェックポイントによって切り捨てられません。 詳細については、「 トランザクション ログ」を参照してください.

CHECKPOINT ステートメントでは、チェックポイントが終了するまでの時間を秒単位で指定する checkpoint_duration 引数が使用できるようになりました。この引数は省略可能です。 詳細については、「 CHECKPOINT」を参照してください。

アクティブ なログ

ログ ファイルの MinLSN から最後に書き込まれたログ レコードまでのセクションを、ログのアクティブな部分 (アクティブなログ) と呼びます。 これは、データベースの完全復旧を実行するのに必要なログのセクションです。 アクティブなログはどの部分も切り捨てることができません。 すべてのログ レコードは、MinLSN より前にあるログの部分から切り離す必要があります。

下図に、2 つのアクティブなトランザクションがあるトランザクション ログの末尾を単純化したものを示します。 チェックポイント レコードは単一のレコードに圧縮されています。

2 つのアクティブなトランザクションと圧縮されたチェックポイント レコードを含むトランザクションの終了ログを示す図。

LSN 148 はトランザクション ログの最後のレコードです。 LSN 147 に記録されたチェックポイントが処理された時点では、Tran 1 は既にコミットされており、Tran 2 だけがアクティブなトランザクションでした。 このため、Tran 2 の最初のログ レコードが、前回のチェックポイントの時点でアクティブなトランザクションの最も古いログ レコードになります。 したがって LSN 142、つまり Tran 2 の Begin トランザクション レコードが MinLSN になります。

長時間トランザクション

アクティブなログには、コミットされていないすべてのトランザクションのあらゆる部分が含まれている必要があります。 トランザクションを開始し、コミットまたはロールバックしないアプリケーションは、データベース エンジンが MinLSN を進めないようにします。 このことが原因で、次の 2 つの問題が発生します。

  • トランザクションにより多くの変更が加えられ、これをコミットせずにシステムをシャットダウンした場合、次にシステムを再起動したときの復旧フェーズは [復旧間隔] オプションで指定した時間よりもかなり長くかかることがあります。
  • ログは MinLSN を超えて切り捨てることができないため、ログが非常に大きくなる可能性があります。 この現象は、自動チェックポイントのたびに通常はトランザクション ログが切り捨てられる単純復旧モデルをデータベースで使用している場合でも発生します。

SQL Server 2019 (15.x) 以降と Azure SQL データベース では、長時間トランザクションの復旧と上記の問題は、高速データベース復旧を使うことで回避できます。

レプリケーションのトランザクション

ログ リーダー エージェントは、トランザクション レプリケーション用に構成した各データベースのトランザクション ログを監視し、レプリケーションのマークが付けられたトランザクションをトランザクション ログからディストリビューション データベースにコピーします。 アクティブ ログには、レプリケーション用にマークされているが、まだディストリビューション データベースに配信されていないすべてのトランザクションが含まれている必要があります。 これらのトランザクションがタイムリーにレプリケートされない場合は、ログの切り捨てを防ぐことができます。 詳細については、「 Transactional Replication」 (トランザクション レプリケーション) を参照してください。

次のステップ

トランザクション ログとログ管理のベスト プラクティスに関するその他の情報については、次の記事および書籍を参照することをお勧めします。