2026年4月3日金曜日

SQL Server トランザクションログの仕組みと管理｜内部構造・肥大化対策・バックアップ戦略

ログは“復旧の命綱”、仕組みを知れば怖くない

SQL Serverのトランザクションログは、データベースの変更履歴を逐次記録し、障害時の復旧や一貫性維持を支える心臓部です。
しかし現場では「ログが肥大化する」「バックアップしてもサイズが戻らない」「復旧モデルの違いが不明」といった悩みが尽きません。
本記事では、内部構造→復旧モデル→肥大化対策→バックアップ戦略の順で整理し、最後に運用監視と最適化の実務ポイントをまとめます。

目次

第1章　トランザクションログの基本構造と役割

トランザクションログ（LDF）は、変更操作の論理履歴を時系列に追記するファイルです。各データベースに少なくとも1つ存在し、INSERT／UPDATE／DELETE／DDLなどの操作を記録します。
主な役割は次のとおりです。

  • 整合性維持：障害時にログからリカバリ（REDO／UNDO）を行い、一貫状態へ戻す。
  • ポイントインタイム復旧：Full/Bulk-loggedでログバックアップを継続していれば、任意時点に復元可能。
  • レプリケーション／可用性：ログシッピングやAGの基盤となる。

書き込みは基本的に順次追記（シーケンシャルI/O）で行われ、チェックポイントによりデータファイル（MDF/NDF）へ変更が反映されます。ログのシーケンシャル特性は高い書き込み性能に寄与します。

第2章　ログレコードとチェックポイントの仕組み

各変更はログレコードとして記録され、各レコードにはLSN（Log Sequence Number）が付与されます。LSNは一意な増分番号で、復旧処理時の“どこまで適用したか”を管理します。

  • ログレコード：DML/DDLの最小単位。トランザクション境界（BEGIN/COMMIT/ROLLBACK）も記録。
  • チェックポイント：メモリ上のDirty Pageをディスクへフラッシュ。以後、その時点より古いログは復旧に不要となり得る。
  • VLF（Virtual Log File）：物理ログは複数のVLFに分割され、使用済みVLFが連続して解放可能になると再利用できる。

“ログが大きい＝常に悪”ではありません。必要な復旧目標（RPO/RTO）とワークロードに応じて、適切なサイズとVLF分割（過剰分割は復旧を遅くする）を計画します。

第3章　復旧モデル（Simple／Full／Bulk-logged）の違い

復旧モデルは「ログの保持とトランケーションの方針」を決めます。誤解しやすいポイントは“バックアップしないとログは小さくならない（Full/Bulk-logged）”ことです。

復旧モデル特徴ログバックアップユースケース
Simpleチェックポイント後に使用済みログを自動トランケート不要検証/開発DB、障害時の時点復旧が不要な環境
Full完全復旧（ポイントインタイム）可能。使用済みログはバックアップでトランケート必須（定期実施）本番DB、厳格なRPO/RTOが必要
Bulk-logged一括操作（BULK INSERT/INDEX REBUILD等）を最小ログ記録推奨（操作前後）バルクロード/大規模メンテ中の一時的切替

よくある落とし穴：Fullモデルのままログバックアップを運用していないと、ログが延々と“未トランケート”のまま溜まり、肥大化します。
この場合にDBCC SHRINKFILEだけで対応すると再成長を繰り返して断片化とI/Oコスト増を招くため、まずログバックアップの運用を正すことが先決です。

第4章　ログ肥大化の原因と対策

SQL Serverのトランザクションログ肥大化は、運用現場で非常に多いトラブルの一つです。
「ログが肥大化してディスクを圧迫する」「DBCC SHRINKFILEで縮小してもまた大きくなる」など、根本原因を理解していないと対症療法の繰り返しになります。

主な原因は以下の3つです。

  • ① バックアップ未実施：Full／Bulk-loggedモデルではバックアップを実行しない限りログが再利用されません。使用済み領域がトランケートされず、肥大化します。
  • ② 長時間トランザクション：未コミットの状態があると、その開始LSNより前のログは削除できません。バッチ処理や手動トランザクションが原因となる場合が多いです。
  • ③ 大量更新・インデックス再構築：一度に多くの行を更新／削除するトランザクションは、ログに膨大な書き込みを発生させます。

【対策1】定期ログバックアップの実施

Fullモデルでは、ログバックアップを取らない限りトランケーションは起こりません。
バックアップはサイズ縮小ではなく「再利用許可」を与える動作である点を理解しておきましょう。

-- ログ使用率の確認
DBCC SQLPERF(LOGSPACE);

-- ログバックアップを実行
BACKUP LOG SalesDB TO DISK = 'D:\\Backup\\SalesDB_log_2025_10_12.bak';

【対策2】長時間トランザクションの監視

未コミットのトランザクションがログ解放を妨げていないか確認します。

SELECT 
    at.name AS TransactionName,
    dt.transaction_id,
    dt.transaction_begin_time,
    DATEDIFF(MINUTE, dt.transaction_begin_time, GETDATE()) AS Duration_Minutes
FROM sys.dm_tran_active_transactions AS at
JOIN sys.dm_tran_database_transactions AS dt
  ON at.transaction_id = dt.transaction_id;

処理が長時間止まっている場合はアプリケーションロジックを見直し、短いトランザクション設計へ変更します。

【対策3】縮小（DBCC SHRINKFILE）は最終手段

ログ縮小は頻繁に行うべきではありません。再成長を繰り返すと断片化が発生し、I/O性能が低下します。
やむを得ない場合のみ以下のように実行します。

USE SalesDB;
DBCC SHRINKFILE (SalesDB_log, 2048); -- 単位MB

その後は初期サイズと自動拡張単位を適切に設定し、安定したログサイズを保ちましょう。

第5章　ログバックアップとトランケーションの関係

ログバックアップの理解は、健全なログ運用の要です。
多くのDBAが混同する「バックアップ」と「トランケーション」の違いを明確にしましょう。

  • バックアップ：ログを別ファイルに保存し、再利用可能領域としてマークする。
  • トランケーション：再利用可能と判断されたVLF（Virtual Log File）を再使用できる状態にする。

つまり、バックアップ＝ログ解放のトリガーであり、即座に物理縮小されるわけではありません。

-- トランザクションログのバックアップ
BACKUP LOG SalesDB TO DISK = 'D:\\Backup\\SalesDB_log_2025_10_12.bak';

-- 使用率を確認
DBCC SQLPERF(LOGSPACE);

-- 再利用可能なVLFを確認
DBCC LOGINFO (SalesDB);

バックアップ後にログ使用率が下がっていればトランケーションが正常に行われた証拠です。
逆に変化がない場合は、未コミットトランザクションやレプリケーション遅延などが原因で再利用がブロックされている可能性があります。

監視のポイント

ログバックアップ後の確認は自動化がおすすめです。SQL AgentジョブやPowerShellスクリプトで使用率を定期的に取得し、閾値超過で通知を飛ばす設計にします。

SELECT 
    DB_NAME(database_id) AS DatabaseName,
    total_log_size_in_bytes / 1024 / 1024 AS TotalMB,
    active_log_size_in_bytes / 1024 / 1024 AS ActiveMB,
    (active_log_size_in_bytes * 100.0 / total_log_size_in_bytes) AS UsedPercent
FROM sys.dm_db_log_stats(DB_ID());

この仕組みにより、肥大化の予兆を検知しやすくなります。

第6章　実務での運用・監視・最適化

安定したログ運用には「監視」「バックアップ」「アラート」の三位一体が不可欠です。定期実行の仕組みを作り、発生兆候を早期に検知することが最も重要です。

  • 監視ビュー：
    ログの状態は sys.dm_db_log_stats や sys.database_recovery_status で確認できます。
  • ジョブ運用：
    SQL Server Agent で 15分おきに BACKUP LOG ジョブを実行。
  • 肥大化検知：
    使用率が 80% を超えた場合にメールまたは Teams で通知。
  • VLF 最適化：
    初期サイズを十分に確保し、自動拡張を小刻みにしないことで断片化を防止。

代表的な監視クエリは以下の通りです。

-- VLF数と状態の確認
DBCC LOGINFO (SalesDB);

-- 各データベースのログ再利用待ち状態
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;

log_reuse_wait_desc の値が「LOG_BACKUP」となっている場合は、ログバックアップが実施されていないサインです。定期ジョブが停止していないか確認しましょう。

VLF設計の最適化

ログファイルが自動拡張で細切れになっていると、復旧処理が遅くなります。
最初に適切な初期サイズを割り当て、必要に応じて固定拡張単位を設定することで安定した性能を維持できます。

ALTER DATABASE SalesDB 
MODIFY FILE (NAME = SalesDB_log, SIZE = 4096MB, FILEGROWTH = 512MB);

第7章　まとめ：安全かつ効率的なログ管理の実践

トランザクションログは「データの命綱」であり、障害時の復旧を支える最も重要な仕組みです。
運用を安定させるには、以下の原則を徹底します。

  • 復旧モデルを理解し、要件に応じて選択する。
  • ログバックアップを定期的に取得し、肥大化を防止する。
  • トランザクションを短く保ち、未コミット状態を残さない。
  • VLF構造を健全に保つため、サイズ設計を最適化する。

こうした基本を守ることで、ログ肥大やパフォーマンス低下を防ぎ、信頼性の高い運用基盤を維持できます。
トランザクションログは「消していいファイル」ではなく「復旧の命綱」であることを常に意識しましょう。

SQL Server学習シリーズ

参考リンク（公式ドキュメント）

SQL Server 解説用イメージ

on
Labels: ,

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)