DO’s&DONT’s #11: やってはいけないこと - トランザクションログファイルの拡張サイズを極端に小さくする

  • Article

 

神谷 雅紀
Escalation Engineer

 

トランザクションログファイルを自動拡張にすることは問題ありませんが、拡張サイズを 1MB など極端に小さな値に設定してはいけません。

 

なぜ？

トランザクションログは、仮想ログファイル (Virtual Log File / VLF) と呼ばれる論理的なファイルに分割されて使用されます。VLF は、トランザクションログファイル作成時および拡張時に作成されますが、トランザクションログファイル拡張時の拡張サイズが小さいと、小さな VLF が多数作成されることになります。VLF の数が多くなると次のようなことが起こります。

  • データベースの起動に時間がかかる。
  • トランザクションログのバックアップ、復元 (restore) に時間がかかる。
  • データベースミラーリングにおいて、ミラーサーバー側へのトランザクションの配信に時間がかかる。
  • トランザクションレプリケーションにおいて、サブスクライバへのトランザクションの配信に時間がかかる。
  • DBCC CHECKDB の実行に時間がかかる。
  • データベーススナップショットの作成に時間がかかる。

上にリストした操作は、トランザクションログをスキャンするため、VLF の数が多くなると、処理時間が長くなります。

トランザクションログの物理アーキテクチャの詳細については、Books Online トランザクションログの物理アーキテクチャを参照して下さい。

 

対処

既に VLF の数が多くなっている可能性がある場合

VLF の数を少なくするために、一旦トランザクションログファイルのサイズを小さくします。その後、元のファイルサイズまたはそれ以上のサイズを明示的に指定してファイルサイズを大きくします。

1. ファイルを圧縮してファイルサイズを小さくする。

shrink

shrink_setting

※ 「ファイル圧縮」の「圧縮アクション」は、「未使用領域を解放する」は TRUNCATEONLY, 「データを同じファイルグループのファイルに移行してファイルを空にする」は ENPTYFILE オプション付きの DBCC SHRINKFILE です。

2. 必要なファイルサイズまで明示的に拡張する。

prop

filesize

 

復旧モデルが単純 (SIMPLE) ではない場合は、トランザクションログをバックアップし、トランザクションログを切り捨てて、トランザクションログファイル内に縮小する分の空きを作っておくことが必要です。

トランザクションログをバックアップし、ログを切り捨てる。

backup

 

logbackup

 

これから新しくデータベースを作成する場合

そのデータベースで必要になるであろうサイズを指定して、データベースファイルを作成します。また、自動拡張サイズには、100MB などある程度大きな値を指定します。

 

拡張サイズの決定

データベースファイルの自動拡張サイズを決定する場合に注意が必要な点は、拡張サイズをあまり大きくしすぎると、拡張処理に時間がかかるようになり、クエリタイムアウトに指定されている期間中に拡張処理が完了せず、クエリがタイムアウトしてしまう場合があるということです。「クエリの実行時間 + ファイル拡張時間 < クエリタイムアウト指定時間」の関係が成り立つように、クエリタイムアウト時間とファイル拡張サイズを決定する必要があります。

以下は、Errorlog ファイルに記録されるファイル拡張を含むトランザクションがキャンセル (タイムアウト) したことを示すメッセージ 5144 です。このメッセージが記録されている場合には、クエリタイムアウト値を大きくするか、ファイルの拡張サイズを小さくする必要があります。

データベース 'DBNAME' のファイル 'DB_log' の自動拡張をユーザーがキャンセルしたか、nnnnn ミリ秒でタイムアウトしました。ALTER DATABASE を使用して、このファイルの FILEGROWTH の値の設定を小さくするか、または新しいファイル サイズを明示的に設定してください。

 

ベストプラクティス

パフォーマンスを考えた場合、トランザクションログファイルに限らずデータファイルも含めて、データベースファイルは、予め必要なファイルサイズで作成することが重要です。自動拡張は、あくまでも、予期しないファイル容量不足に備えた保険として考え、極力自動拡張は発生させないようにします。