SQL Server 2012 R2：ログファイル圧縮の失敗について

Question

お世話になります。
まさんと申します。

SQL Server 2012 R2を利用したシステム開発を行っております。

msdbデータベースにてトランザクションログの自動拡張(10%で拡張)を設定し、復旧モデルに「完全復旧モデル」を指定した上で、
ログ圧縮コマンド「DBCC SHRINKDATABASE」を実行すると稀に以下のエラーが発生します。

----------------------------------------------------------------------------------------------------

メッセージ 9020、レベル 16、状態 2、行 36
データベース 'msdb' のログを拡張できませんでした。ログの圧縮が実行されています。再試行してください。

----------------------------------------------------------------------------------------------------

どうやら「DBCC SHRINKDATABASE」を実行しログ圧縮を実行するタイミングでログの自動拡張が発生すると
本エラーが発生するようです。

エラーが発生しないよう対策を検討しているのですが、
エラー内容の指定どおり、ログ圧縮を「再試行」するしか策はないでしょうか？

復旧モデルに「単純復旧モデル」を指定すればログ拡張が行われない(？)ので
回避できるかなとも考えておりますが、あまりしたくないです。

理想としてはエラーが対策されたSQL Serverの修正パッチのようなものがあればよいのですが…

上記エラーの回避策をご存知の方がいらっしゃいましたら
ご教授頂けませんでしょうか。

よろしくお願い致します。

Answer 1

一つ前提がよろしくないように見受けられます。
ログの自動拡張は、拡張中のパフォーマンスの急激な劣化、ディスク領域不足や今回のような拡張の失敗など様々な本番環境に向かない特徴があるため、あくまでサイジングでは想定できなかったイレギュラーに対する備えとするべきです。
ですので、本来は自動拡張が発生しないよう予め適切なサイズにログファイルのサイズを設定し、適切なバックアップ計画の下で運用するべきです。
先ずはログファイルのサイズの推移を確認し、ログファイルのサイズの設定とバックアップの運用を適切に行うようお薦めします。

ログファイルに関して圧縮と拡張を同時に行おうというのは無理な話ですので、そういう状態になっているというのを教えてくれる重要なエラーが今回発生しているものですので、どういったものを想定されているかちょっと分かりませんが修正パッチというのも無理な話かと思います。

---

MCITP（Database Developer/Database Administrator）

Answer 2

補足というか…

完全復旧モデルを選択し、DBCC SHRINKDATABASEでログ圧縮を行うということは、適切なバックアップ運用が行われていないように見受けられます。にもかかわらず、単純復旧モデルを選択したくないというのもよくわかりません。

障害発生時の復旧方法についてどのようにお考えでしょうか？ その復旧方法から必然的にバックアップ方法は決まります。そして復旧・バックアップに適切な復旧モデルも決まります。バックアップの際にログは自動的に圧縮されますので、DBCC SHRINKDATABASEコマンドは実行不要です。

ですのでログ圧縮を実施する前にこれらを決定すべきです。

仮に障害発生時には新規にデータベースを構築し直す、定期的なバックアップは不要ということでしたら、復旧の際に任意タイミングにロールバックできる完全復旧モデルは必要なく、単純復旧モデルで十分です。

Answer 3

nagino - 引退エンジニア様

ご回答頂きありがとうございます。

おっしゃる通りですね･･･頂いたアドバイスは恒久的な対策として今後に役立てさせて頂きます。

修正パッチがあれば、と記述した理由ですが、
自動拡張とログ圧縮を用いている仕組みが悪いのは理解できるのですが、そのような状況を可とする
SQL Serverの作りとしてどうなのかな？と考え、もしかすると修正されているのでは、との思いつきで記述致しました。

エラーの内容から自分で自分の首を絞めるような動きだなと思いまして…

本エラーが発生したシステムですが、１年程前にSQL Server 2005からSQL Server 2012に移行致しました。
過去SQL Server 2005を利用していた際は一度も発生しておらず、移行後にちらほら発生しておりまして、
SQL Server 2012の不具合かな、と考えた事も修正パッチを想定した要因の一つです。

しかしよくよく考えると、エラーとして定義されているという事は「やってはいけない行為」なんだと今は納得しております。

Answer 4

佐祐理様

ご回答頂きありがとうございます。

単純復旧モデルを選択しない理由ですが、障害発生時にシステムとして可能な限り直前の状態に戻したいためです。
バックアップは毎日実施しておりますが、数時間のデータロスが許されないシステムでして、システムデータベース、
ユーザデータベース共に「完全復旧モデル」を指定しております。

ただmsdbデータベースのトランザクション情報が障害発生時に必要かと考えると必要はないです。
ですので、私個人としてはmsdbデータベースは「単純復旧モデル」でもいいのではと考えています。

本システムは長年動作しているシステムでして、[nagino - 引退エンジニア]様の返信にも記述いたしましたが、
過去SQL Server 2005を利用している際は一度も本エラーは発生しませんでした。SQL Server 2012に移行してから
発生するようになったのです。過去同様の設定で安定稼動していたことが、現在も「完全復旧モデル」を採用している理由の一つです。

私の認識違いでしたら申し訳ないのですが、
バックアップはログの「切捨て」だけで、ファイルサイズの削減はされないとの認識です。
ファイルサイズの削減はログ圧縮コマンド「DBCC SHRINKDATABASEコマンド」を実行すること、
との考えだったのですが、間違っているでしょうか？

Answer 5

バックアップされていたのですね、失礼しました。

バックアップにより内部的に空になっているログファイルを圧縮し、その後、自動拡張によりログファイルを順次拡張させていきたいということでしょうか？
ログ圧縮をしなければ拡張の必要もなくなりますので、ログ圧縮しないことをお勧めします。

Answer 6

佐祐理様

ご返信ありがとうございます。

失礼しました。
質問内容にバックアップに関する内容を一切記述しておりませんでした…

>> バックアップにより内部的に空になっているログファイルを圧縮し、その後、自動拡張によりログファイルを順次拡張させていきたいということでしょうか？

おっしゃる通りです。適切なログサイズが把握できておらず自動拡張に設定しております。
毎日のバックアップによりログを切捨てておりますが、一度肥大化したログの物理ファイルサイズは切り捨てしても小さくならないとの認識から、
バックアップ後にログ圧縮をしている次第です。

ログ圧縮をしない対策ですが、抱えている各DBの特性を見直し、
復旧モデルの割り当てを含め検討したいと思います。

本エラーに関する対策ですが、ひとまずプログラムで回避する暫定対策となりました。

色々と補足頂きありがとうございました。

Answer 7

佐祐理さんがおっしゃっている事は、「ログ圧縮をして物理ファイルサイズを小さくする必要性は無いのでは？」という事ではないでしょうか。

自動拡張及び圧縮のコストを考えると、私も物理サイズ圧縮は不要だと考えます。

※どちらにせよ最大ファイルサイズは変わらないと考える為です。