トランザクション ログ ファイルのサイズの管理
適用対象:
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 は、現在使用されているログ領域の量に関する情報を返し、いつトランザクション ログを切り捨てる必要があるかを示します。
ログ ファイルの現在のサイズ、最大サイズ、およびファイルの自動拡張オプションについては、sys.database_files にある、そのログ ファイルに関する size、max_size、growth の各列も使用できます。
重要
ログ ディスクの過負荷を避けてください。 ログ ストレージがトランザクション負荷の 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オプションを利用し、データとログのファイルに自動拡張を設定するとき、パーセンテージよりサイズで設定したほうが増加の制御に優れている場合があります。割合は常に増加する量であるためです。SQL Server 2022 (16.x) より前のバージョンでは、トランザクション ログでファイルの瞬時初期化を使用できないため、ログ拡張の回数増加が重要になります。
SQL Server 2022 (16.x) (すべてのエディション) 以降と Azure SQL Database では、ファイルの瞬時初期化によって、トランザクション ログの拡張イベントに最大 64 MB まで利用できます。 新しいデータベースの既定の自動拡張サイズの拡張は 64 MB です。 64 MB を超えるトランザクション ログ ファイルの自動拡張イベントについては、ファイルの瞬時初期化の恩恵を得ることができません。
ベスト プラクティスとしては、トランザクション ログに対して
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 配布と必要なサイズを得るために必要な増分を決定するには、この SQL Tiger Team による VLF の分析と決定 スクリプトをご覧ください。
自動拡張の増分値が大きくなると、次の 2 つの問題が発生する可能性があります。
- 自動拡張の増分値が大きくなると、新しい領域の割り当て中にデータベースが一時停止し、クエリのタイムアウトが発生する場合があります。
- 自動拡張の増分値が大きいと、生成回数が極めて少なく、大きな VLF が生成されます。これにより、パフォーマンスに影響が出る場合があります。 指定されたインスタンスにおいて、すべてのデータベースの現在のトランザクション ログ サイズに最適な VLF 配布と必要なサイズを得るために必要な増分を決定するには、この SQL Tiger Team による VLF の分析と決定 スクリプトをご覧ください。
自動拡張を有効にしても、増加が遅く、クエリのニーズを満たせなければ、トランザクション ログがいっぱいになったというメッセージが表示されます。 増分変更の詳細については、「ALTER DATABASE (Transact-SQL) の File および Filegroup オプション」を参照してください
データベースにログ ファイルが複数存在すると、パフォーマンスが向上しません。トランザクション ログ ファイルでは、同じファイル グループのデータ ファイルのように比例配分を利用することがないためです。
ログ ファイルは自動的に圧縮するように設定できます。 ただし、これは推奨されません。auto_shrink データベース プロパティは既定で FALSE に設定されています。 auto_shrink を TRUE に設定すると、ファイル領域の 25% を超える領域が未使用の場合にのみ、自動圧縮によってファイルのサイズが縮小されます。
- ファイルは、ファイル領域の 25% のみが未使用領域になるサイズ、またはファイルの元のサイズの、どちらか大きい方のサイズまで圧縮されます。
- auto_shrink プロパティの設定変更については、「データベースのプロパティの表示または変更」と「ALTER DATABASE の SET オプション (Transact-SQL)」を参照してください。
次のステップ
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示