トランザクション ログ ファイルのサイズの管理

適用対象: はいSQL Server (サポートされているすべてのバージョン)

このトピックでは、SQL Server のトランザクション ログ サイズの監視、トランザクション ログの圧縮、トランザクション ログ ファイルの追加と拡大、tempdb トランザクション ログ増加率の最適化、トランザクション ログ ファイルのサイズ拡大の管理の方法について説明します。

ログ領域の使用量の監視

sys.dm_db_log_space_usage を利用し、ログ領域の使用量を監視します。 この DMV は、現在使用されているログ領域の量に関する情報を返し、いつトランザクション ログを切り捨てる必要があるかを示します。

ログ ファイルの現在のサイズ、最大サイズ、およびファイルの自動拡張オプションについては、sys.database_files にある、そのログ ファイルに関する sizemax_sizegrowth の各列も使用できます。

重要

ログ ディスクの過負荷を避けてください。 ログ ストレージがトランザクション負荷の IOPS 要件と短い待ち時間要件に対応できることを確認してください。

ログ ファイルのサイズを圧縮する

物理ログ ファイルの物理サイズを削減するには、ログ ファイルを圧縮する必要があります。 トランザクション ログ ファイルに未使用領域が含まれていることがわかっている場合にはこの方法が有効です。 ログ ファイルの圧縮を実行できるのは、データベースがオンラインで、1 つ以上の仮想ログ ファイル (VLF) が解放されている間だけです。 場合によっては、次のログの切り捨てまでログを圧縮できないことがあります。

注意

実行時間の長いトランザクションなど、長期間にわたって VLF がアクティブなままになる要因があると、ログの圧縮が制限されたり、ログがまったく圧縮できないことがあります。 詳細については、「ログの切り捨てが遅れる原因となる要因」を参照してください。

ログ ファイルを圧縮すると、論理ログのどの部分も保持しない 1 つまたは複数の VLF (つまり、非アクティブな VLF) が削除されます。 トランザクション ログ ファイルを圧縮すると、ログ ファイルが目的のサイズにできるだけ近いサイズに縮小されるように、非アクティブな VLF がログ ファイルの末尾から削除されます。

重要

トランザクション ログを圧縮する前に、ログの切り捨てが遅れる原因となる要因に留意してください。 ログの圧縮後、ストレージ領域が再び必要になると、トランザクション ログが再び増え、その分のパフォーマンスのオーバーヘッドが発生します。 詳細については、このトピックの「推奨事項」を参照してください。

データベース ファイルを圧縮せずにログ ファイルを圧縮する

ログ ファイルの圧縮イベントを監視する

ログ領域の監視

ログ ファイルの追加または拡大

既存のログ ファイルを拡大するか (ディスク領域が十分にある場合)、通常、別のディスク上にあるデータベースにログ ファイルを追加することによって、領域を確保することができます。 ログ領域が不足し、さらにログ ファイルが保存されているボリュームでディスク容量が不足しない限り、トランザクション ログ ファイルは 1 つで十分です。

  • データベースにログ ファイルを追加するには、ALTER DATABASE ステートメントの ADD LOG FILE 句を使用します。 ログ ファイルを追加すると、ログを大きくすることができます。
  • ログ ファイルを大きくするには、ALTER DATABASE ステートメントの MODIFY FILE 句を使用します。SIZE および MAXSIZE 構文を指定します。 詳細については、「ALTER DATABASE (Transact-SQL) の File および Filegroup オプション」を参照してください。

詳細については、このトピックの「推奨事項」を参照してください。

tempdb トランザクション ログのサイズの最適化

サーバー インスタンスを再起動すると、 tempdb データベースのトランザクション ログのサイズが、元の自動拡張前のサイズに変更されます。 これにより、 tempdb のトランザクション ログのパフォーマンスが低下することがあります。

このオーバーヘッドは、サーバー インスタンスを起動または再起動した後、 tempdb のトランザクション ログのサイズを増やすことで回避できます。 詳細については、「 tempdb Database」をご覧ください。

トランザクション ログ ファイルのサイズ拡大の管理

トランザクション ログ ファイルのサイズ拡大を管理するには、ALTER DATABASE (Transact-SQL) の File および Filegroup オプション ステートメントを使用します。 次のことを考慮してください。

  • 現在のサイズを KB、MB、GB、TB 単位で変更するには、SIZE オプションを使用します。
  • 拡張増分値で変更するには、FILEGROWTH オプションを使用します。 0 は、自動拡張がオフで、領域を追加できないことを示します。
  • ログ ファイルの最大サイズを KB、MB、GB、TB 単位で制御するか、拡張値を UNLIMITED に設定するには、MAXSIZE オプションを使用します。

詳細については、このトピックの「推奨事項」を参照してください。

推奨事項

トランザクション ログ ファイルを使用して作業するときの一般的な推奨事項を次に示します。

  • トランザクション ログの自動拡張 (autogrow) の増分は FILEGROWTH オプションで設定されますが、トランザクションの作業負荷に対して常に余裕を持たせられるよう、十分な量にする必要があります。 ログ ファイルの拡張増分値は、拡張を頻繁に行わなくても済むように十分な大きさにする必要があります。 トランザクション ログのサイズは、次の時間のログ量を監視することで正しく判断できます。

    • 完全バックアップに必要な時間。完全バックアップが終わるまでログはバックアップされないためです。
    • 最も大規模なインデックス保守管理に必要な時間。
    • データベースで最も大規模な一括処理を実行するときに必要な時間。
  • FILEGROWTH オプションを利用し、データとログのファイルに 自動拡張 を設定するとき、パーセンテージ より サイズ で設定したほうが増加の制御に優れている場合があります。割合は常に増加する量であるためです。

    • トランザクション ログではファイルの瞬時初期化を活用できないことに留意してください。そのため、ログ拡張の回数増加が重要になります。
    • ベスト プラクティスとしては、トランザクション ログに対して FILEGROWTH オプションの値を 1,024 MB 以上に設定しないでください。 FILEGROWTH オプションの既定値:
    Version 既定値
    SQL Server 2016 (13.x) 以降 データ 64 MB。 ログ ファイル 64 MB。
    SQL Server 2005 (9.x) 以降 データ 1 MB。 ログ ファイル 10%。
    SQL Server 2005 (9.x) の前 データ 10%。 ログ ファイル 10%。
  • 増分が少ないと小さな VLF が過度に生成され、パフォーマンスが低下します。 指定されたインスタンスにおいて、すべてのデータベースの現在のトランザクション ログ サイズに最適な VLF 配布と必要なサイズを得るために必要な増分を決定するには、このスクリプトをご覧ください。

  • 増分が大きいと大きな VLF が生成される回数が極めて少なく、やはりパフォーマンスに影響が出ます。 指定されたインスタンスにおいて、すべてのデータベースの現在のトランザクション ログ サイズに最適な VLF 配布と必要なサイズを得るために必要な増分を決定するには、このスクリプトをご覧ください。

  • 自動拡張を有効にしても、増加が遅く、クエリのニーズを満たせなければ、トランザクション ログがいっぱいになったというメッセージが表示されます。 増分変更の詳細については、「ALTER DATABASE (Transact-SQL) の File および Filegroup オプション」を参照してください。

  • データベースにログ ファイルが複数存在すると、パフォーマンスが向上しません。トランザクション ログ ファイルでは、同じファイル グループのデータ ファイルのように比例配分を利用することがないためです。

  • ログ ファイルは自動的に圧縮するように設定できます。 ただし、これは 推奨されませんauto_shrink データベース プロパティは既定で FALSE に設定されています。 auto_shrink を TRUE に設定すると、ファイル領域の 25% を超える領域が未使用の場合にのみ、自動圧縮によってファイルのサイズが縮小されます。

関連項目

BACKUP (Transact-SQL)
満杯になったトランザクション ログのトラブルシューティング (SQL Server エラー 9002)
SQL Server トランザクション ログのアーキテクチャと管理ガイドのトランザクション ログ バックアップ
トランザクション ログのバックアップ (SQL Server)
ALTER DATABASE (Transact-SQL) の File および Filegroup オプション