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

適用対象:SQL Server

この記事では、SQL Server トランザクション ログのサイズの監視、トランザクション ログの圧縮、トランザクション ログ ファイルの追加または拡大、 tempdb トランザクション ログの増加率の最適化、トランザクション ログ ファイルの拡張の制御を行う方法について説明します。

この記事の対象は、SQL Server です。 プロセスは似ていますが、Azure SQL でのファイル領域管理については、次を参照してください。

• Azure SQL Managed Instance のデータベースのファイル領域を管理します。
• Azure SQL Database のデータベースのファイル領域を管理します。

データベースの記憶域スペースの種類を理解する

データベースのファイル領域を管理するには、次のストレージ領域の量を理解することが重要です。

次の図は、データベースのさまざまな種類の記憶域スペース間のリレーションシップを示しています。

ファイル領域の情報について単一データベースのクエリを実行する

次のクエリを使用して、割り当て済みデータベース ファイル領域と未使用の割り当て済み領域を返します。 クエリ結果の単位は MB です。

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

ログ領域の使用量の監視

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

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

ログ ファイルを圧縮する

オペレーティング システムに空き領域を返すことで、ログ ファイルを圧縮して物理サイズを小さくします。 圧縮では、トランザクション ログ ファイルに未使用の領域が含まれている場合にのみ誤差が生じます。

開いているトランザクションが原因でログ ファイルがいっぱいになった場合は、 トランザクション ログの切り捨てを妨げている原因を調査します。

データベース ファイルの圧縮によるパフォーマンスへの悪影響の可能性があることに注意してください。 圧縮後のインデックスのメンテナンスを参照してください。

トランザクション ログを圧縮する前に、ログの 切り捨てを遅らせる可能性がある要因に注意してください。 ログの圧縮後にストレージ領域が再び必要な場合は、トランザクション ログが再び拡張され、ログの増加操作中にパフォーマンスのオーバーヘッドが発生します。 詳細については、「 推奨事項」を参照してください。

ログ ファイルを圧縮できるのは、データベースがオンラインで、少なくとも 1 つの 仮想ログ ファイル (VLF) が無料である場合のみです。 場合によっては、ログの圧縮は、次のログの切り捨て後にのみ可能な場合があります。

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

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

圧縮操作の詳細については、次のリソースを確認してください。

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

• DBCC SHRINKFILE (Transact-SQL)

• ファイルを圧縮する

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

• Log File Auto Shrink イベント クラス

ログ領域の監視

• Sys.dm_db_log_space_usage (Transact-SQL)

• Sys.database_files (Transact-SQL) (ログ ファイルまたはログ ファイルの size、 max_size、および growth の列を参照してください)。

圧縮操作後のインデックスのメンテナンス

データ ファイルに対する圧縮操作が完了すると、インデックスが断片化する可能性があります。 この断片化により、大規模なスキャンを使用するクエリなど、特定のワークロードのパフォーマンス最適化の有効性が低下します。 圧縮操作の完了後にパフォーマンスが低下する場合は、インデックスを再構築するためのインデックスのメンテナンスを検討してください。 インデックスの再構築にはデータベース内の空き領域が必要であるため、割り当てられた領域が増加し、圧縮操作の影響が無効になる可能性があることに注意してください。

詳細については、「 インデックスのメンテナンスを最適化してクエリのパフォーマンスを向上させ、リソース消費量を削減する」を参照してください。

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

既存のログ ファイルを拡大するか (ディスク領域が許可されている場合)、通常は別のディスク上のデータベースにログ ファイルを追加することで、領域を増やすことができます。 ログ領域が不足していて、ログ ファイルを保持しているボリューム上のディスク領域も不足している場合を除き、1 つのトランザクション ログ ファイルで十分です。

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

詳細については、「 推奨事項」を参照してください。

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

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

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

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

ALTER DATABASE (Transact-SQL) ファイルおよびファイル グループ オプション ステートメントを使用して、トランザクション ログ ファイルの拡張を管理します。 次の点に注意してください。

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

詳細については、「 推奨事項」を参照してください。

推奨事項

トランザクション ログ ファイルを操作する際に考慮すべき一般的な推奨事項を次に示します。

• FILEGROWTH オプションによって設定されるトランザクション ログの自動拡張 (自動拡張) インクリメントは、ワークロード トランザクションのニーズを先取りするのに十分な大きさである必要があります。 ログ ファイルの拡張増分値は、拡張を頻繁に行わなくても済むように十分な大きさにする必要があります。 トランザクション ログのサイズを適切に設定するには、次の実行中に占有されるログの量を監視することをお勧めします。

  • ログ バックアップは完了するまで実行できないため、完全バックアップの実行に必要な時間。
  • 最も大規模なインデックス保守管理に必要な時間。
  • データベースで最大のバッチを実行するために必要な時間。

• FILEGROWTH オプションを使用してデータ ファイルとログ ファイルの自動拡張を設定する場合は、増加率をより適切に制御できるように、割合ではなくサイズを設定することをお勧めします。パーセンテージは増え続ける量であるためです。

  • SQL Server 2022 (16.x) より前のバージョンでは、トランザクション ログでは ファイルの瞬時初期化を使用できないため、ログの拡張時間が特に重要です。

  • SQL Server 2022 (16.x) (すべてのエディション) 以降と Azure SQL Database では、ファイルの瞬時初期化によって、トランザクション ログの拡張イベントに最大 64 MB まで利用できます。 新しいデータベースの既定の自動拡張サイズの増分は 64 MB です。 64 MB を超えるトランザクション ログ ファイルの自動拡張イベントについては、ファイルの瞬時初期化の恩恵を得ることができません。

  • ベスト プラクティスとして、トランザクション ログの FILEGROWTH オプション値を 1,024 MB を超えて設定しないでください。 FILEGROWTH オプションの既定値は次のとおりです。

    バージョン	既定値
    SQL Server 2016 (13.x) 以降	データ: 64 MB。 ログ ファイル: 64 MB。
    SQL Server 2005 (9.x) 以降	データ: 1 MB。 ログ ファイル: 10%。
    SQL Server 2005 (9.x) の前	データ: 10%. ログ ファイル: 10%。

• 自動拡張の増分が小さいと、小さな VTF が 多くなりすぎて、パフォーマンスが低下する可能性があります。 特定のインスタンス内のすべてのデータベースの現在のトランザクション ログ サイズに対する最適な VLF 分布と、必要なサイズを達成するために必要な増加増分を判断するには、SQL Tiger チームが提供する VLF の分析と修正に関するこの スクリプトを参照してください。

• 自動拡張が大きくインクリメントされると、次の 2 つの問題が発生する可能性があります。

  • これにより、新しい領域が割り当てられている間にデータベースが一時停止し、クエリのタイムアウトが発生する可能性があります。
  • 生成される VTF が 少なすぎて大きくなる可能性があり、パフォーマンスにも影響を与える可能性があります。 特定のインスタンス内のすべてのデータベースの現在のトランザクション ログ サイズに対する最適な VLF 分布と、必要なサイズを達成するために必要な増加増分を判断するには、SQL Tiger チームが提供する VLF の分析と修正に関するこの スクリプトを参照してください。

• 自動拡張を有効にした場合でも、クエリのニーズを満たすのに十分な速度で増加できない場合は、トランザクション ログがいっぱいであるというメッセージを受け取ることができます。 増加増分の変更の詳細については、 ALTER DATABASE (Transact-SQL) ファイルおよびファイル グループのオプションを参照してください。

• トランザクション ログ ファイルでは、同じファイル グループ内のデータ ファイルのように、 多くのフィルが使用されないため データベースに複数のログ ファイルを含めることはパフォーマンスを向上させることはありません。

ログ ファイルは自動的に圧縮するように設定できます。 ただし、この構成はお勧めしません。 AUTO_SHRINK データベース プロパティは既定で FALSE に設定されています。 AUTO_SHRINKが TRUE に設定されている場合、自動圧縮では、領域の 25% を超える領域が使用されていない場合にのみ、ファイルのサイズが小さくなります。

• ファイルは、ファイルの 25% のみが未使用領域であるサイズか、ファイルの元のサイズのいずれか大きい方に圧縮されます。
• AUTO_SHRINK プロパティの設定の変更については、「データベースのプロパティの表示または変更」および「ALTER DATABASE SET オプション (Transact-SQL)」を参照してください。

関連するコンテンツ

• BACKUP (Transact-SQL)
• 完全なトランザクション ログのトラブルシューティング (SQL Server エラー 9002)
• SQL Server トランザクション ログのアーキテクチャと管理ガイドのトランザクション ログ バックアップ
• トランザクション ログ バックアップ (SQL Server)
• ALTER DATABASE (Transact-SQL) ファイルとファイル グループのオプション