次の方法で共有

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

  • [アーティクル]

適用対象: SQL Server

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

この記事の対象は、SQL Server です。 同様ですが、Azure SQL Managed Instance でのトランザクション ログ ファイルのサイズの管理については、「 Azure SQL Managed Instance のデータベースの管理ファイル領域を参照してください。 Azure SQL データベースについては、「Azure SQL Database でデータベースのファイル領域を管理する」を参照してください。

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

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

データベースの量 定義 説明
使用済みのデータ領域 データベース データの格納に使用する領域。 一般的に、使用済みの領域は挿入 (削除) で増加 (減少) します。 場合によっては、操作に関係するデータの量とパターンや断片化に応じて、挿入または削除で使用される領域が変わらない場合があります。 たとえば、すべてのデータ ページから 1 行を削除しても、使用領域が減らない場合があります。
割り当て済みのデータ領域 データベース データの格納に使用できる書式設定されたファイル領域。 割り当て済みの領域の量は自動的に増えますが、削除後に自動的に減ることはありません。 この動作により、スペースを再フォーマットする必要がないため、将来の挿入が高速になります。
割り当て済みで未使用のデータ領域 割り当てられた量と使用されるデータ領域の差。 この数量は、データベース データ ファイルを圧縮して再利用できる最大空き領域を表します。
データの最大サイズ データベース データを格納する最大領域。 データの最大サイズを超えて、割り当て済みのデータ領域の量を増やすことはできません。

次の図は、データベースの異なる種類の記憶域スペース間の関係を示しています。

データベース数量テーブルのさまざまなデータベース領域の概念のサイズを示すダイアグラム。

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

次のクエリを使用して、割り当て済みデータベース ファイル領域と未使用の割り当て済み領域を返します。 クエリ結果の単位は 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で使用することもできます。

重要

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

ログ ファイルの圧縮

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

未処理のトランザクションが原因でログ ファイルがいっぱいになっている場合は、トランザクション ログの切り捨てを妨げている原因を調査してください。

注意事項

圧縮操作は、通常のメンテナンス操作と見なすことはできません。 通常の定期的なビジネス操作のために増加するデータ ファイルとログ ファイルには、圧縮操作は必要ではありません。 圧縮コマンドは、実行中にデータベースのパフォーマンスに影響を与えます。使用率が低い期間中に実行する必要があります。 通常のアプリケーション ワークロードによってファイルが同じ割り当て済みサイズまで再度増える場合は、データ ファイルを圧縮することはお勧めしません。

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

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

ログ ファイルの圧縮を実行できるのは、データベースがオンラインで、1 つ以上の仮想ログ ファイル (VLF) が解放されている間だけです。 場合によっては、ログの圧縮は、次のログの切り捨て後にのみ可能な場合があります。

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

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

圧縮操作について詳しくは、次のリンクをご覧ください。

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

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

ログ領域の監視

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

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

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

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

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

データベースにログ ファイルを追加するには、ALTER DATABASE ステートメントの ADD LOG FILE 句を使用します。 これにより、ログを拡大できます。

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

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

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

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

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

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

現在のファイル サイズを KB、MB、GB、TB 単位で変更するには、'SIZE' オプションを使用します。

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

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

推奨事項

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

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

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

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

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

    • 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%。

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

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

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

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

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

ログ ファイルは自動的に圧縮するように設定できます。 ただし、これはお勧めしません。 auto_shrink データベース プロパティは既定で FALSE に設定されています。 auto_shrink を TRUE に設定すると、ファイル領域の 25% を超える領域が未使用の場合にのみ、自動圧縮によってファイルのサイズが縮小されます。 - ファイルの 25% のみが未使用領域であるサイズまたはファイルの元のサイズのいずれか大きい方に圧縮されます。 - auto_shrink プロパティの設定の変更については、「 データベースのプロパティの表示または変更 および ALTER DATABASE SET オプション (Transact-SQL)を参照してください。

関連するコンテンツ