☰

SQL Server ログの使用領域が 100 パーセントを超えるのはなぜですか?

tag-icon • tag-icon ms-sql
SQL Server ログの使用領域が 100 パーセントを超えるのはなぜですか?

トランザクションログを報告するアプリケーションエラー（例外）が発生しています。ACTIVE_TRANSACTION により満杯です「 」によって報告された保留中のトランザクションはありませんDBCC OPENTRAN。

実行中、DBCC SQLPERF('logspace')ログサイズはわずか1.3MBですが、ログ使用領域は107.7%と報告されています。

このデータベースは、ログ ファイルの最大サイズが 2 TB を超え、初期サイズが 2 MB、自動拡張が 10% に設定されています。復旧モデルはシンプルに設定されています。

ログの使用領域が 100% を超えるのはなぜですか? また、これほど多くの領域が使用可能であるのに、なぜ例外が生成されるのでしょうか?

答え1

犯人を探す

走れるsp_WhoIsActive誰が何を実行しているかを確認し、実行中のプロセス/アクティブなトランザクションのロジックを確認します。 T-SQL クエリをより高速に実行したり、より小さなトランザクションとして実行したりできるように最適化できるかどうかを確認します。これにより、ログ ファイル内の未使用の空き領域がより迅速に解放され、再利用できるようになります。

それは設計上可能だ

SQL Server シンプル復旧モデル

すべてのトランザクションは引き続きトランザクション ログに書き込まれますが、トランザクションが完了し、データがデータ ファイルに書き込まれると、トランザクション ログ ファイルで使用されていた領域は新しいトランザクションで再利用できるようになります。

ソース

リカバリ モデルの設定で、長時間実行されるトランザクションによってトランザクション ログが極端に大きくなるケースを見たことがありますSIMPLE。そのトランザクションは実際は失敗し、ロールバックにも同じくらいの時間がかかりました。したがって、長時間実行されるトランザクション、パフォーマンスの悪いクエリ、または最適化されていない不適切なクエリによって、この問題が発生する可能性があります。

SIMPLEリカバリモデルデータベースのトランザクションログファイルにスペースが割り当てられると、未使用のトランザクションログ空き領域またはOSレベルの空き領域からの自動増加ごとトランザクション ログ ファイルは、ファイル縮小操作が発生するまで新しい領域を保持します (例: ) DBCC SHRINKFILE (database_log, 2048)。

重要：たとえば、ファイル縮小操作が発生するとDBCC SHRINKFILE (database_log, 2048)、トランザクション ログ内の未使用のログ領域のみが空き領域として OS に解放されます。トランザクション ログに書き込まれたアクティブな実行中のトランザクションは、ファイル縮小操作中には解放されません。

ログファイルの縮小

ログ ファイルを縮小する場合の問題は、次に大規模なトランザクションまたは不適切に記述されたクエリが実行されると、ログ ファイルが再びいっぱいになり、縮小操作を繰り返す必要があることです。この問題を永続的に修正するには、根本的な問題を見つけて解決してください。その間、ログ ファイルの縮小を続行してください。

根本原因を解決する

根本的な問題はクエリである可能性が高いため、誰が何を行っているかを特定し、彼らに連絡して、発見事項とともに問題を報告すると、サーバーのディスク領域パーティションを圧迫しないようにロジックを修正するよう彼らにプレッシャーをかけることができます。パフォーマンスのためにクエリチューニングを行うロジックを最適化することを検討してください。

  • 根本原因の追跡

    場合によっては、根本的な原因は長時間実行されているトランザクションではないこともあります。たとえば、誰かがレプリケーションを設定して、それを適切に解除しなかったことが原因である可能性があります。まずは確認してみましょう。log_reuse_wait_desc in sys.databases:

    SELECT name, log_reuse_wait_desc FROM sys.databases;

    ただし、これはログを今すぐに縮小できない理由のほんの一例にすぎないことに留意してください。

    そこに何か面白いものが見つからなければ、sp_WhoIsActiveをテーブルに記録する ユーザーが を実行しBEGIN TRAN、セッションを何時間も開いたままにしているときにそれをキャッチします。長時間実行されているトランザクションを探し、所有者と話し、1 つの巨大なトランザクションではなく、小さなチャンクで作業を実行できないかどうかを確認します。

  • ログファイルスペースメタデータ

    DBCC SQLPERF(logspace)は、データベース ログ ファイルの消費量のみに関心がある場合に、非常に便利なコマンドです。SQL Server インスタンス上の各データベースの各ログ ファイルの累積サイズと、消費された領域の量 (ログ ファイルの合計サイズに対する割合) が表示されます。 欠点は、結果がデータベースの集計であるという事実です。 ログ ファイルが複数ある場合、結果はファイル レベルではなくデータベース レベルで表示されます。

    この DBCC コマンドは、不適切なログ バックアップ スケジュールや不正なログ ファイルのサイズ設定によって発生する問題を確認するときに便利ですが、ログ ファイルのサイズ設定、バックアップ スケジュールの頻度や復旧モデルの調整について十分な情報に基づいた決定を行うために必要なすべての情報を提供しません。

    ソース

関連情報