// SQL Server エラーリファレンス

SQL Server エラー 9002: データベースのトランザクションログがいっぱいです

データベースのログファイルがいっぱいで拡張できないため、書き込みが失敗します。メッセージに示される理由(LOG_BACKUP、ACTIVE_TRANSACTION など)が、ログの再利用を妨げているものを表します。

エラー番号
9002
重大度
17
カテゴリ
メモリ、ログ、ストレージ
エラーメッセージ
The transaction log for database '%ls' is full due to '%ls' and the holdup lsn is %S_LSN.

意味

SQL Serverは、データを変更する前に、すべての変更をトランザクションログに書き込みます。ログ領域は、それを必要とするものがなくなって初めて再利用されます。完全復旧モデルではログバックアップの後が該当し、どのモデルでも、最も古い未完了のトランザクション、レプリケーション、可用性グループのレプリカがログを保持し続けることがあります。ログを再利用できず、拡張もできない場合(最大サイズに達した、またはディスクが満杯)、すべての書き込みが9002で失敗します。

メッセージには理由が示されます。これは次と同じ値です log_reuse_wait_desc 内 sys.databases.

よくある原因

  • LOG_BACKUP: データベースが完全復旧モデルまたは一括ログ復旧モデルであるにもかかわらず、ログバックアップが取得されていない。
  • ACTIVE_TRANSACTION: 長時間実行中、または放置されたままのオープントランザクションがログを保持し続けている。
  • REPLICATION または AVAILABILITY_REPLICA: ログリーダーまたはセカンダリレプリカが遅れている。
  • ログファイルに固定の最大サイズがあるか、ログファイルがあるディスクがいっぱいです。
  • 1 つのステートメントで数百万行を削除するなど、単一の巨大なトランザクション。

修正方法

  1. ログを再利用できない理由をご覧ください。

    SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = N'SalesDb';
  2. 次の場合は LOG_BACKUP、ログバックアップを取得します(スケジュールも可能です)。

    BACKUP LOG SalesDb TO DISK = N'D:\Backup\SalesDb_log.trn';
  3. 次の場合は ACTIVE_TRANSACTION、最も古い未完了のトランザクションを見つけて、コミットまたはロールバックしてください。

    DBCC OPENTRAN (N'SalesDb');
  4. ログに余裕を持たせてください。ログファイルを拡張可能にするか、別のディスクに 2 つ目のログファイルを追加します。

    ALTER DATABASE SalesDb MODIFY FILE (NAME = SalesDb_log, MAXSIZE = UNLIMITED, FILEGROWTH = 512MB);
  5. 大規模なデータ変更をバッチに分割し、各トランザクションが小さくなるようにします。

    WHILE 1 = 1
    BEGIN
        DELETE TOP (10000) FROM dbo.EventLog WHERE CreatedAt < '2024-01-01';
        IF @@ROWCOUNT = 0 BREAK;
    END;
  6. このデータベースでポイントインタイム復元が不要な場合、単純復旧モデルでは各チェックポイントの後にログ領域が再利用されます。

    ALTER DATABASE SalesDb SET RECOVERY SIMPLE;

質問

9002を修正した後、ログファイルを縮小したほうがよいですか?

一度限りのイベントで通常のサイズをはるかに超えて肥大化した場合だけです。常時その領域を必要とするログを縮小しても、また肥大化するだけで、書き込みを待たせる間に時間を浪費します。

関連エラー

その他のメモリ、ログ、ストレージのエラー

コードとメッセージのテキストは、 Microsoft Database Engine のイベントとエラーのリファレンス。全容は次で参照 SQL Server エラー一覧 または すべてのデータベースエラーを検索.

// エラーが減り、修正も速く

SQLly で SQL Server を操作する

macOS、Windows、Linux 用のネイティブ SQL クライアント。スキーマを認識する IntelliSense、危険なステートメントへのガードレール、分かりやすいエラーメッセージを備え、SQL Server をはじめ、お使いの他のデータベースにも対応します。