Categories
Database Backup

** Discover expert strategies for preventing and resolving MSSQL transaction log full errors (Error 9002). Learn rapid recovery techniques, VLF management, and architectural best practices for DBAs.

Microsoft SQL Serverを管理するデータベース管理者（DBA）やDevOpsエンジニアにとって、エラー9002「データベース ‘X’ のトランザクション ログがいっぱいです」ほど、即座に不安をかき立てるアラートはほとんどありません。トランザクション ログが一杯になり、拡張できなくなると、データベースは実質的に読み取り専用となります。すべての INSERT、UPDATE、DELETE 操作が停止し、アプリケーションのトランザクションは失敗し、本番環境は完全に停止してしまいます。

SQL Serverのトランザクション ログの基礎となるアーキテクチャを理解し、根本原因を正確に診断し、迅速な復旧手順を実行することは、高可用性を維持するために不可欠なスキルです。本ガイドでは、トランザクション ログのメカニズム、緊急時にログがいっぱいになった場合の解決方法、および再発を防ぐためのアーキテクチャ上のベストプラクティスについて詳しく解説します。

SQL Serverトランザクション ログのアーキテクチャを理解する

トランザクション ログがいっぱいになった問題を効果的にトラブルシューティングするには、まずSQL Serverがどのようにデータを書き込み、管理しているかを理解する必要があります。

先行書き込みログ（WAL: Write-Ahead Logging）

SQL Serverは先行書き込みログ（WAL）プロトコルを使用します。データが変更されるたびに、その変更はまずメモリ内のトランザクション ログに書き込まれ、データベース ファイル（MDF/NDF）内の実際のデータ ページが更新される前に、ディスク上の物理ログ ファイルにフラッシュされます。これによりACID（原子性、一貫性、分離性、永続性）の準拠が保証され、クラッシュが発生した場合でも、SQL Serverはトランザクションを再実行（ロールフォワード）または取り消し（ロールバック）できるようになります。

仮想ログ ファイル（VLF）と循環ログ

内部的には、物理トランザクション ログ ファイル（LDF）は、仮想ログ ファイル（VLF）と呼ばれる小さな論理セグメントに分割されています。トランザクション ログは循環的に動作します。ログ レコードが書き込まれると、1つのVLFがいっぱいになり、次のVLFへと移動します。

ログが物理ファイルの終端に達すると、先頭に戻ろうとします。ただし、VLFを上書きできるのは、そのVLFが非アクティブとマークされている場合のみです。すべてのVLFがアクティブ（SQL Serverがまだ必要としているログ レコードが含まれている状態）である場合、ログは先頭に戻ることができません。自動拡張が有効でディスク容量に空きがある場合は、物理ファイルが拡張されます。ディスクがいっぱいであるか、自動拡張が制限されている場合は、エラー9002が発生します。

ログの切り捨て（Truncation）とログの縮小（Shrinking）

ログを切り捨てれば物理ファイルサイズが小さくなると誤解されがちですが、そうではありません。
* ログの切り捨て（Log Truncation）: アクティブなVLFを非アクティブとしてマークし、再利用可能な状態にするプロセスです。ディスク上のLDFファイルのサイズは縮小されません。
* ログの縮小（Log Shrinking）: LDFファイルのサイズを物理的に縮小し、オペレーティング システムに領域を返却するプロセスです。

完全復旧モデルでは、ログの切り捨てはトランザクション ログのバックアップが正常に完了したときにのみ発生します（他のプロセスがログをアクティブに保持していないことが前提です）。

「トランザクション ログがいっぱいです」エラー（エラー9002）の診断

ログがいっぱいになったとき、最初に行うべきことは、盲目的にディスク容量を追加したりファイルを縮小したりすることではありません。ログが切り捨てられない理由を特定する必要があります。SQL Serverには、sys.databases カタログ ビューを通じて、ログの再利用を妨げている原因を正確に特定する組み込みメカニズムが用意されています。

ボトルネックを特定するには、以下のT-SQLコマンドを実行します。

SELECT 
    name AS DatabaseName, 
    recovery_model_desc AS RecoveryModel, 
    log_reuse_wait_desc AS LogReuseWaitReason
FROM sys.databases
WHERE name = 'YourDatabaseName';

また、以下のコマンドでトランザクション ログの現在の使用状況を確認できます。

DBCC SQLPERF(LOGSPACE);

一般的な log_reuse_wait_desc の状態

  1. LOG_BACKUP: データベースが完全復旧モデルまたは一括ログ復旧モデルであり、最近トランザクション ログのバックアップが取得されていません。これが最も一般的な原因です。
  2. ACTIVE_TRANSACTION: 長時間実行されているトランザクション（大規模なインデックス再構築や、コミットされていない忘れられたトランザクションなど）がログをアクティブに保持しています。
  3. REPLICATION / CDC: トランザクション レプリケーションまたは変更データ キャプチャ（CDC）が有効になっており、ログ リーダー エージェントがまだトランザクションを処理していません。
  4. AVAILABILITY_REPLICA: AlwaysOn可用性グループにおいて、セカンダリ レプリカが切断されているか同期が遅すぎるため、プライマリ レプリカがセカンダリでログ レコードが反映されるまで保持せざるを得ない状態です。

迅速な復旧戦略：本番環境での問題解決

返された log_reuse_wait_desc に応じて、緊急対応は異なります。最も一般的なシナリオに対する迅速な復旧戦略を以下に示します。

シナリオ1：ログ バックアップの欠落または失敗（LOG_BACKUP）

待機タイプが LOG_BACKUP の場合、解決策は単純です。トランザクション ログをバックアップする必要があります。

BACKUP LOG [YourDatabaseName] 
TO DISK = 'N:BackupsYourDatabaseName_EmergencyLog.trn' 
WITH COMPRESSION, STATS = 10;

バックアップが完了すると、非アクティブなVLFが切り捨てられ、SQL Serverは通常の動作を再開します。バックアップ ドライブがいっぱいの場合は、一時的なネットワーク共有やnullデバイスへのバックアップが必要になる場合があります（データベースが容易に再現可能でない限り、ログ チェーンが切断されるため強く推奨されません）。

-- 警告: これはログ チェーンを断ち切り、ポイントインタイム復旧を不可能にします。
-- 絶対に必要な場合にのみ使用し、直後に完全バックアップを取得してください。
BACKUP LOG [YourDatabaseName] TO DISK = 'NUL';

シナリオ2：長時間実行されているアクティブなトランザクション（ACTIVE_TRANSACTION）

単一のトランザクションが数時間実行されている場合、その期間中ずっとログの切り捨てが妨げられます。まず、問題となっているトランザクションを特定します。

DBCC OPENTRAN('YourDatabaseName');

このコマンドは、最も古いアクティブなトランザクションとそのサーバー プロセスID（SPID）を返します。動的管理ビュー（DMV）をクエリすることで、そのSPIDが何をしているか詳細を確認できます。

SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    r.start_time,
    r.status,
    r.command,
    t.text AS QueryText
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id = <SPID_FROM_DBCC_OPENTRAN>;

もしそのトランザクションが不正なクエリや停止したプロセスである場合は、ログを解放するために強制終了する必要があるかもしれません。

KILL <SPID>;

注意：大規模なトランザクションを強制終了するとロールバックがトリガーされます。これにはかなりの時間がかかる可能性があり、一時的に追加のログ アクティビティが発生します。ロールバック中にSQL Serverサービスを再起動しないでください。再起動すると、データベースは復旧モードに入ってしまいます。

シナリオ3：緊急の領域割り当て（ディスクが100%フル）

LDFファイルがドライブ全体を消費してしまった場合、SQL Server自体がバックアップ イベントを記録するためにわずかなログ領域を必要とするため、バックアップすら実行できません。このシナリオでは、空き容量のある別のドライブにセカンダリ ログ ファイルを追加する必要があります。

ALTER DATABASE [YourDatabaseName]
ADD LOG FILE 
(
    NAME = N'YourDatabaseName_Log2',
    FILENAME = N'E:TempLogsYourDatabaseName_Log2.ldf',
    SIZE = 5GB,
    MAXSIZE = 50GB,
    FILEGROWTH = 1GB
);

これにより、SQL Serverに即座に余裕が生まれます。データベースがオンラインになったら、トランザクション ログのバックアップを取得し、セカンダリ ログ ファイルを空にしてから削除します。

-- 1. ログを切り捨てるためにログ バックアップを取得
BACKUP LOG [YourDatabaseName] TO DISK = '...';

-- 2. 一時ログ ファイルを空にする
DBCC SHRINKFILE (N'YourDatabaseName_Log2', EMPTYFILE);

-- 3. 一時ログ ファイルを削除する
ALTER DATABASE [YourDatabaseName] REMOVE FILE [YourDatabaseName_Log2];

トランザクション ログの予防と管理のためのベストプラクティス

事後対応のトラブルシューティングはストレスが大きく、SLAにも影響します。エンタープライズ データベースの安定性のために、予防的なアーキテクチャおよび運用のベストプラクティスを実装することが不可欠です。

1. 堅牢で自動化されたバックアップ戦略の実装

データベースが完全復旧モデルの場合、頻繁なトランザクション ログ バックアップが必須です。目標復旧時点（RPO）とトランザクション量に応じて、ログ バックアップは5〜15分ごとに実行する必要があります。

CloudSaveのようなエンタープライズ バックアップ ソリューションは、このプロセスを大幅に簡素化します。VDI（仮想デバイス インターフェイス）を介してSQL Serverと直接統合することで、CloudSaveはDBAがポリシー主導の高頻度トランザクション ログ バックアップを構成できるようにします。これにより、複雑なカスタムSQLエージェント ジョブを必要とせずに、ログが継続的に切り捨てられ、安全に暗号化され、オフサイトまたは不変のクラウド ストレージに保存されるため、LOG_BACKUP 待機状態を防ぐことができます。

2. トランザクション ログの適切なサイズ設定とVLFの管理

トランザクション ログのサイズ管理を自動拡張に頼るのは危険なアンチパターンです。自動拡張操作は負荷が高く、ディスクがゼロ初期化される間（インスタンス ファイル初期化が有効でない限り。なお、ログ ファイルには適用されません）、トランザクション処理が一時停止します。

さらに、頻繁で小さな自動拡張（一度に10%や50MBずつなど）は、VLFの断片化を招きます。数千もの小さなVLFを持つトランザクション ログは、データベースの起動時間、バックアップ パフォーマンス、レプリケーションの遅延を著しく低下させます。

  • ログの事前サイズ設定: 最大のメンテナンス操作（インデックス再構築など）を分析し、拡張が発生しないようにLDFファイルを事前にサイズ設定します。
  • 固定の自動拡張を設定: 自動拡張をパーセンテージではなく固定サイズ（1GBや5GBなど）に変更し、VLFが適切なサイズで作成されるようにします。

以下のクエリを使用してVLF数を確認できます（SQL Server 2017以降）。

SELECT 
    db_name(database_id) AS DatabaseName,
    COUNT(vlf_sequence_number) AS VLF_Count
FROM sys.dm_db_log_info(DB_ID('YourDatabaseName'));

VLF数が500を超える場合は、静かな時間帯を待ち、ログを最小サイズまで縮小してから、必要なサイズまで大きなチャンクで手動で拡張し直すことを検討してください。

3. インデックス メンテナンス操作の最適化

インデックスの再構築は、一括ログ復旧モデルであっても（インデックスの種類によりますが）、完全にログに記録される操作です。500GBのインデックスを再構築すると、少なくとも500GBのトランザクション ログ レコードが生成されます。

メンテナンス中のログ肥大化を軽減するには：
* インデックス再構築時に SORT_IN_TEMPDB = ON を使用します。これによりソート フェーズがTempDBにオフロードされ、ユーザー データベースのトランザクション ログへの負荷が軽減されます。
* 可能な場合はインデックスの再構築からインデックスの再構成に切り替えます。再構成はログ効率が高く、操作全体をロールバックすることなく中断できるためです。
* 大規模な DELETE や UPDATE 操作をバッチ処理します。1つのトランザクションで1,000万行を削除するのではなく、5万行ずつのチャンクに分けて削除し、コミットしてバッチ間でログ バックアップがログを切り捨てられるようにします。

4. 高可用性とレプリケーション トポロジの監視

AlwaysOn可用性グループでは、すべての同期および非同期セカンダリ レプリカでログ レコードが反映されるまで、プライマリ レプリカはログを切り捨てることができません。

セカンダリ レプリカがオフラインになったり、ネットワーク帯域幅がプライマリのトランザクション生成速度に追いつかなかったりすると、プライマリの送信キューが大きくなり、ログがいっぱいになります（AVAILABILITY_REPLICA 待機タイプ）。

SQLServer:Replica > Log Send Queue パフォーマンス カウンターの堅牢な監視を実装してください。セカンダリ レプリカが完全に失われた場合は、可用性グループから削除するか、データ移動を一時停止してプライマリ ログが切り捨てられるようにする必要があります。

結論

トランザクション ログがいっぱいになることはデータベース管理者にとっての通過儀礼ですが、長時間のダウンタイムを招く必要はありません。先行書き込みログとVLFのメカニズムを理解することで、sys.databases を使用して根本原因を迅速に診断し、適切な迅速復旧戦略を適用できます。

長期的な安定性は、事後対応の修正から脱却することにかかっています。ログ ファイルの事前サイズ設定、メンテナンス ルーチンの最適化、そしてCloudSaveのようなエンタープライズ グレードのバックアップ プラットフォームを活用して厳格で自動化されたログ バックアップ スケジュールを強制することで、トランザクション ログを健全かつ切り捨てられた状態に保ち、高スループットの本番ワークロードをサポートし続けることができます。

カテゴリー