トランザクションログの肥大化

Question

はじめまして。
SQLServer2012 Expressにて、トランザクションログの肥大化が起こっています。

復旧モデルは「単純」になっているのでトランザクションログは自動で切り捨てられると思っていたのですが、実際には徐々にトランザクションログの使用済み領域が増加しています。

技術情報には、トランザクションログの切り捨てを行うにはTransact-SQLにて「BACKUP LOG xxxx」を実行する方法がありますが、これは「単純」モデルでは使用できない事になっています。

ですが、実際にはこの「BACKUP LOG xxxx」が「単純」モデルでも実行できてしまいます（トランザクションログも解放されます）。

動きをみる限り、データベースのプロパティ表示では「単純」モデルになっていますが、実際は「単純」モデル以外で動いているのではないかと思えます。設定やインストール方法に何か問題があったのかと思ったのですが、特に思い当たるものはありません。使用OSは、WindowsServer2012です。

以前には同じプログラムでSQLServer2008R2 Expressを使用していましたが、このような事はありませんでした。

なぜ、「単純」モデルなのにトランザクションログが自動で解放されないのか、とても困っています。定期的に「BACKUP LOG xxxx」を実行すれば一方的な肥大化は回避できますが、どうも釈然としません。

なぜ、「単純」モデルなのにトランザクションログが自動で解放されないのか、どなたか対処方法がお分かりにならないでしょうか。

ご回答の程、宜しくお願い致します。

Answer 1

可能性のお話になりますが、単純モデルで切捨てが行われるのは、チェックポイントが切られたタイミングですので

大量のデータを一括コミットでインサートまたは更新した場合、トランザクションログが大きくなる場合があると思われます。

そのようなロジックになっていないでしょうか?

Answer 2

２０件程度のデータ追加を数十秒おきに行うような連続運用テストでトランザクションログが大きくなる現象が出ています。

トランザクションとしては、１件のデータ追加で都度コミットを行っていますので、大量のデータを一括でコミットするようなロジックにはなっていないと思っています。

sys.database カタログ ビューの log_reuse_wait_desc列の値は「LOG_BACKUP」となっているので、ログバックアップで切り捨てなければならないログが溜まっていますよ、ということなのでしょうけれど、そもそも「単純」モデルではログバックアップはサポートされておらず、ログバックアップによる切り捨て操作も不要、と思っていました。

技術情報をいろいろ読むと、トランザクションログはコミットした後すぐに解放される訳ではないような事が書かれているので、トランザクションログが大きくなるのはあり得るのかなとは思っているのですが、不思議なのは「単純」モデルなのになぜTransact-SQLで「BACKUP LOG xxxx」が実行できてしまうのか？と言う事です。

技術情報には、「単純」モデルでは「BACKUP LOG xxxx」をサポートしないと書かれています。

ですが、実際にはログバックアップが必要になる場合があり、その時はログバックアップが実行できる、という事なのでしょうか？

Answer 3

トランザクションログのアーキテクチャの説明が以下にございます。

https://msdn.microsoft.com/ja-jp/library/jj835093%28v=sql.120%29.aspx

連続で1件ごとにコミットを切りテストされているとのことですので切捨てが行われる前に最後尾に新しいログが追加され切捨てができていない可能性が高いのではないかとおもいます。

トランザクションログは、切捨てが行われても再利用されるだけでサイズは、変わりません。

新しいログが追加されるので切捨てできずにトランザクションログのエリアの再利用ができない状態になって、結果トランザクションログのファイルサイズが増加する状態になっているように思います。

テストの最後にログバックアップを行わないでDBCC SHRINKFILEでトランザクションログファイルの圧縮を行い圧縮可能でしたらトランザクションログの再利用を行っているということになりますのでトランザクションログの切捨ては実行されていると判断できるのではないでしょうか。(ログファイルが増加はしていますが)

log_reuse_wait_desc列の値については、以下の説明では、抜粋：ログの先頭を前方に移動するためにログ バックアップが必要である (完全復旧モデルまたは一括ログ復旧モデルのみ)。となっているので単純モデルでは、関係ないのではないかと思われます。

ログの切り捨てが遅れる原因となる要因

https://technet.microsoft.com/ja-jp/library/ms345414%28v=sql.105%29.aspx

単純モデルの設定が効いていないような気もしますが･････

ためしにSQLServer２００８R2で単純モデルのBackup LOGを実行しましたらエラーで実行できませんでした。

Answer 4

BadCompany様、返信ありがとうございました。

「単純」モデルであっても、トランザクションログが切り捨てられずに使用済み領域が増大する事がある、と言う事で理解しました。

こちらの環境では、使用済み領域が増大した場合はログバックアップを行わなければ使用済み領域が解放されず、ログファイルの圧縮も行えません。ログバックアップは、連続運用テストを行う前は実行すると「単純モデルではログバックアップは実行できない」という旨のエラーとなりますが、連続運用テストを行って使用済み領域が増大するとなぜか実行できます。

なので、定期的にログバックアップを実行し、使用済み領域が一方的に増大するのを回避する事にしました。なぜ、「単純」モデルなのにログバックアップができてしまうのか、若干モヤモヤしたところは残りますが、トランザクションログの肥大化が回避できれば良いので、これで解決と言う事にさせて頂きます。

初めて投稿させて頂きましたが、貴重な情報を提供頂き、感謝しております。ありがとうございました。