【SQL Server】トランザクションログがいっぱいになる原因と対処法｜ログ肥大化・復旧モデル・DBCC SQLPERF

SQL Serverで更新処理やバッチ処理を実行していると、「トランザクションログがいっぱいです」「The transaction log for database is full」というエラーが出ることがあります。代表的なのは 9002 エラーです。

トランザクションログがいっぱいになる原因は、単にログファイルが大きいことではありません。ログバックアップが取られていない、長時間トランザクションが残っている、レプリケーションやAlways Onの同期が詰まっている、ディスク容量や自動拡張設定に問題がある、など原因によって対処が変わります。

緊急時に最初の5分で見る順番

本番でログフルが発生した場合は、手当たり次第に縮小や復旧モデル変更をするのではなく、次の順番で原因を切り分けます。この順番なら、バックアップ不足、長時間トランザクション、ディスク不足のどれかを早く見分けられます。

この時点で原因が LOG_BACKUP なのか、ACTIVE_TRANSACTION なのか、物理的な容量不足なのかを分けられます。以降の対処は、この原因ごとに選びます。

エラー9002の意味

SQL Serverの 9002 は、対象データベースのトランザクションログ領域が不足し、新しいログレコードを書けない状態を示します。オンライン中のデータベースでログがいっぱいになると、更新系の処理が失敗したり、実質的に書き込みできない状態になります。

Error: 9002, Severity: 17, State: 4.
The transaction log for database 'SampleDB' is full due to 'LOG_BACKUP'.

メッセージ末尾の due to には、ログを再利用できない理由が表示されることがあります。SQLで確認する場合は、次の log_reuse_wait_desc を見ます。

まず確認するSQL

最初に、どのDBのログ使用率が高いかを確認します。DBCC SQLPERF(LOGSPACE) は、DBごとのログサイズと使用率を一覧で見られる定番コマンドです。

DBCC SQLPERF(LOGSPACE);

次に、復旧モデルとログ再利用待ちの理由を確認します。

SELECT
    name,
    recovery_model_desc,
    log_reuse_wait_desc
FROM sys.databases
WHERE name = N'SampleDB';

log_reuse_wait_desc が原因特定の入口です。ここを見ずに復旧モデルを変えたりログ縮小したりすると、再発しやすくなります。

log_reuse_wait_descの主な意味

log_reuse_wait_desc には、トランザクションログを切り捨てられない理由が入ります。よく見る値と対処の方向性は次の通りです。

ロックや長時間トランザクションが絡む場合は、SQL Serverでテーブルロックを確認するSQL も合わせて見ると、ログ肥大化とロック保持の関係を追いやすくなります。

原因別の即時対応早見表

ログフルの対応は、原因によってまったく変わります。特に LOG_BACKUP と ACTIVE_TRANSACTION を取り違えると、ログバックアップを取っても空かない、縮小してもすぐ戻る、といった状態になります。

復旧モデルを確認する

トランザクションログの挙動は、復旧モデルによって大きく変わります。FULL と BULK_LOGGED では、ログバックアップを定期的に取らないとログが切り捨てられず、ログファイルが肥大化しやすくなります。

SELECT
    name,
    recovery_model_desc
FROM sys.databases
WHERE name = N'SampleDB';

FULL 復旧モデルなのにログバックアップを取っていない場合、ログが切り捨てられず増え続けるのは自然な挙動です。単発の対処ではなく、ログバックアップの運用を組む必要があります。

LOG_BACKUPが原因の場合

log_reuse_wait_desc が LOG_BACKUP の場合、FULL または BULK_LOGGED 復旧モデルでログバックアップが不足している可能性が高いです。まずはログバックアップ履歴を確認します。

SELECT
    database_name,
    MAX(backup_finish_date) AS last_log_backup_time
FROM msdb.dbo.backupset
WHERE type = 'L'
  AND database_name = N'SampleDB'
GROUP BY database_name;

ログバックアップを取得する例です。出力先は環境に合わせて変更してください。

BACKUP LOG [SampleDB]
TO DISK = N'D:\Backup\SampleDB_LOG.trn'
WITH INIT, COMPRESSION;

ログバックアップを取ると、不要になった論理ログ領域が再利用可能になります。ただし、ログバックアップはログファイルの物理サイズを即座に小さくする操作ではありません。物理サイズを小さくしたい場合は、原因解消後に縮小を検討します。

ログバックアップジョブを確認する

一度ログバックアップを取って解消しても、ジョブが止まっていれば再発します。SQL Server Agentを使っている場合は、ログバックアップジョブの直近実行結果も確認します。

SELECT TOP (20)
    j.name AS job_name,
    h.run_date,
    h.run_time,
    h.run_status,
    h.message
FROM msdb.dbo.sysjobs AS j
JOIN msdb.dbo.sysjobhistory AS h
    ON h.job_id = j.job_id
WHERE j.name LIKE N'%log%backup%'
  AND h.step_id = 0
ORDER BY h.instance_id DESC;

run_status は、一般的に 1 が成功、0 が失敗です。ジョブ名に規則がない環境では、ログバックアップを実行しているジョブ名に合わせて条件を変更してください。

ACTIVE_TRANSACTIONが原因の場合

ACTIVE_TRANSACTION の場合、長時間開いたままのトランザクションがログを保持しています。この状態ではログバックアップを取っても、古いログを十分に再利用できないことがあります。

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    s.status,
    at.transaction_begin_time,
    DATEDIFF(MINUTE, at.transaction_begin_time, SYSDATETIME()) AS elapsed_minutes,
    dt.database_transaction_log_bytes_used / 1024 / 1024 AS log_used_mb
FROM sys.dm_tran_session_transactions AS st
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = st.session_id
JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
JOIN sys.dm_tran_database_transactions AS dt
    ON dt.transaction_id = st.transaction_id
WHERE dt.database_id = DB_ID(N'SampleDB')
ORDER BY at.transaction_begin_time;

原因セッションが分かったら、接続元、実行SQL、業務影響を確認します。必要に応じて担当者に COMMIT / ROLLBACK してもらうか、最終手段として KILL を検討します。ただし、ロールバックにも時間とログ領域が必要になることがあります。

SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_resource,
    t.text AS running_sql
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id = 57;

長時間トランザクションは、ロック待ちやデッドロックの原因にもなります。デッドロック側の調査は SQL Serverのデッドロックの原因と確認方法 で整理しています。

ログファイルのサイズと自動拡張を確認する

ログがいっぱいになる原因には、ファイルサイズの上限、自動拡張の無効化、ディスク容量不足もあります。次のSQLでログファイルの現在サイズ、自動拡張、最大サイズを確認します。

SELECT
    DB_NAME(database_id) AS database_name,
    name AS logical_name,
    physical_name,
    size * 8 / 1024 AS size_mb,
    CASE max_size
        WHEN -1 THEN 'UNLIMITED'
        WHEN 0 THEN 'NO_GROWTH'
        ELSE CAST(max_size * 8 / 1024 AS varchar(20)) + ' MB'
    END AS max_size,
    CASE is_percent_growth
        WHEN 1 THEN CAST(growth AS varchar(20)) + ' %'
        ELSE CAST(growth * 8 / 1024 AS varchar(20)) + ' MB'
    END AS growth_setting
FROM sys.master_files
WHERE type_desc = 'LOG'
  AND database_id = DB_ID(N'SampleDB');

自動拡張が無効、最大サイズに到達、ディスク空き容量不足のいずれかなら、ログバックアップだけでは解決しません。一時的にログファイルを拡張する、ディスクを確保する、自動拡張設定を見直す、といった対応が必要です。

ALTER DATABASE [SampleDB]
MODIFY FILE (
    NAME = N'SampleDB_log',
    SIZE = 4096MB,
    FILEGROWTH = 512MB
);

自動拡張はパーセント指定より、一定MB指定にする方が予測しやすいです。拡張が細かすぎると、頻繁なファイル拡張やVLF増加の原因になります。

VLF数を確認する

ログファイルが何度も小さく自動拡張されると、VLF（Virtual Log File）が増えすぎることがあります。VLFが多すぎると、復旧、バックアップ、ログ管理の性能に影響することがあります。SQL Server 2017以降では sys.dm_db_log_info で確認できます。

SELECT
    DB_NAME(database_id) AS database_name,
    COUNT(*) AS vlf_count,
    SUM(vlf_size_mb) AS total_vlf_mb
FROM sys.dm_db_log_info(DB_ID(N'SampleDB'))
GROUP BY database_id;

VLF数が極端に多い場合は、ログファイルの適正サイズを決めてから、原因解消、必要に応じた縮小、適切なサイズへの再拡張という順番で整えます。闇雲に縮小と自動拡張を繰り返すと、VLFが増えて逆効果になることがあります。

ログ縮小は最後に行う

ログファイルが肥大化したあと、物理サイズを戻したい場合は DBCC SHRINKFILE を使います。ただし、縮小は原因を解消した後に行います。ログバックアップ不足や長時間トランザクションが残っている状態で縮小しても、すぐにまた増えます。

-- 論理ファイル名を確認してから実行する
DBCC SHRINKFILE (N'SampleDB_log', 1024);

ログ縮小は日常運用の定期タスクにするものではありません。一時的な大量処理や設定ミスで肥大化した後に、適正サイズへ戻す目的で限定的に使います。

ログ使用率の監視SQL

再発防止のため、ログ使用率と再利用待ち理由を定期的に記録しておくと便利です。DBCC SQLPERF(LOGSPACE) は結果を一時テーブルに入れて扱えます。

CREATE TABLE #logspace (
    database_name sysname,
    log_size_mb decimal(18, 2),
    log_used_percent decimal(18, 2),
    status int
);

INSERT INTO #logspace
EXEC ('DBCC SQLPERF(LOGSPACE)');

SELECT
    l.database_name,
    l.log_size_mb,
    l.log_used_percent,
    d.recovery_model_desc,
    d.log_reuse_wait_desc
FROM #logspace AS l
JOIN sys.databases AS d
    ON d.name = l.database_name
WHERE l.log_used_percent >= 80
ORDER BY l.log_used_percent DESC;

このSQLを監視ジョブや定期レポートに組み込むと、ログフルになる前に気付きやすくなります。しきい値はDBのサイズや運用に合わせて調整してください。

復旧モデルをSIMPLEに変える前の注意点

ログがいっぱいになったからといって、すぐに SIMPLE 復旧モデルへ変更するのは危険です。SIMPLE ではログバックアップが使えず、ポイントインタイムリストアもできません。本番DBでは、バックアップ設計と復旧要件を確認してから判断します。

-- 実行前にバックアップ設計と復旧要件を確認する
ALTER DATABASE [SampleDB] SET RECOVERY SIMPLE;

-- FULLに戻す場合は、その後のバックアップ運用も見直す
ALTER DATABASE [SampleDB] SET RECOVERY FULL;

SIMPLE から FULL に戻した後は、ログバックアップ運用を成立させるために、フルバックアップを含むバックアップ計画を整えます。復旧モデルの変更は一時対応ではなく、運用方針の変更として扱うべきです。

一括更新や大量DELETEが原因の場合

大量の UPDATE や DELETE は大量のログを生成します。一度に全件更新すると、ログ領域を一気に消費し、ロック保持時間も長くなります。

WHILE 1 = 1
BEGIN
    UPDATE TOP (5000) dbo.Orders
    SET status = N'ARCHIVED'
    WHERE status = N'CLOSED'
      AND archived_at IS NULL;

    IF @@ROWCOUNT = 0
        BREAK;

    CHECKPOINT;
END;

更新処理そのものの書き方は SQLのUPDATE文でデータを更新する方法 でも整理しています。WHERE句漏れや全件更新は、ログ肥大化だけでなくロック競合の原因にもなります。

やってはいけない対処

トランザクションログがいっぱいになると急いで対応したくなりますが、次のような対処は本番では特に注意が必要です。

ロック調査やKILLの判断は SQLのロック調査・解除完全ガイド も参考になります。

再発防止策

トランザクションログがいっぱいになる問題は、単発対応より再発防止が重要です。運用としては、ログバックアップ、監視、ファイル設定、長時間トランザクション対策をセットで見直します。

よくある質問

DBCC SQLPERF(LOGSPACE)で使用率が高い場合、すぐ縮小してよいですか？

まず log_reuse_wait_desc を確認します。ログバックアップ不足や長時間トランザクションが原因なら、縮小より先に原因解消が必要です。

FULL復旧モデルなのにログバックアップを取っていないとどうなりますか？

トランザクションログが切り捨てられず、増え続けます。ポイントインタイムリストアが必要なら、定期的なログバックアップを運用に組み込みます。

ログバックアップを取れば物理ファイルサイズも小さくなりますか？

通常、小さくなりません。ログバックアップは論理的に再利用可能な領域を作ります。物理サイズを小さくするには、原因解消後に DBCC SHRINKFILE を検討します。

SIMPLE復旧モデルにすれば解決しますか？

ログバックアップ待ちは避けやすくなりますが、ポイントインタイムリストアができなくなります。本番では復旧要件を確認してから判断します。

長時間トランザクションはログ肥大化の原因になりますか？

なります。未完了のトランザクションが古いログを保持していると、ログを再利用できません。sys.dm_tran_session_transactions などで確認します。

VLFが多いと何が問題ですか？

復旧、ログバックアップ、ログスキャンなどが遅くなることがあります。小さすぎる自動拡張を繰り返した環境では、VLF数を確認し、ログファイルの初期サイズと拡張単位を見直します。

ログフル時にログファイルを削除してよいですか？

削除してはいけません。SQL Serverが管理している重要なファイルであり、DB破損や起動不能につながります。SQL Serverのバックアップ、ファイル拡張、トランザクション解消などの手順で対応します。

まとめ

SQL Serverのトランザクションログがいっぱいになったときは、まず DBCC SQLPERF(LOGSPACE) で使用率を確認し、sys.databases の log_reuse_wait_desc で原因を見ます。LOG_BACKUP ならログバックアップ、ACTIVE_TRANSACTION なら長時間トランザクションの特定が優先です。

ログ縮小や復旧モデル変更は、原因解消後に慎重に行います。再発防止には、定期的なログバックアップ、ログ使用率監視、適切なファイルサイズ設定、長時間トランザクションや大量更新の見直しが必要です。