SQLServer2005のミラーリング環境でトランザクションログが増え続ける

Question

初めてお世話になります。

主題の件で質問させて頂きます。

3台のサーバーにインストールした、SQL Server 2005 Standard Editionにてデータベースのミラーリングを行っています。

サーバー１：プリンシパル
サーバー２：ミラー
サーバー３：ミラーリング監視

上記構成で、すでに運用中です。

問題は、プリンシパルにてデータベースの完全バックアップと「トランザクションログを切り捨てる」オプションでトランザクションログバックアップを行った後にデータベースの圧縮を行っても、トランザクションログのファイルサイズが減らないという事です。

トランザクションログは4GBytesを超えており、日毎のバックアップファイルの容量もこの為に増えております。

ミラー無しの状態であれば、上記オペレーションを行えば、トランザクションログのファイルサイズは小さくなるのですが、ミラーリング環境ではファイルサイズに変化がありません。

BACKUP LOG WITH NO_LOGも、ミラー無しの環境では機能しますが、ミラー環境では「BACKUP LOG WITH TRUNCATE_ONLY はデータベース ミラーリング用に構成されているので、データベース '****' では実行できません。」とエラーが出てしまい、実行できません。
※BooksOnlineにあるとおり、上記コマンドは奨励されていないことは理解しております。

このような環境でトランザクションログのサイズを減らすにはどのような手段があるのか、ご教示頂ければ幸いです。

Answer 1

> データベースの圧縮を行っても、トランザクションログのファイルサイズが減らないという事です。

データベースの圧縮ではなく、ファイルの圧縮でトランザクション ログを選らばないとトランザクション ログの圧縮はできなかったと思います。

まずトランザクション ログのバックアップをした後に「ディスク使用量」レポートを出してみて、未使用領域が多ければ圧縮→ファイル→ログでトランザクション ログを圧縮します。未使用領域がほとんどない場合は何か必要があってトランザクション ログが使われているので圧縮はできません。

---

Please mark the thread as Answered when an answer resolves your problem. 回答が得られたら、もらった回答の [回答済み] ボタンをクリックしてスレッドを回答済みにしましょう

Answer 2

M_Lewis様

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

>データベースの圧縮ではなく、ファイルの圧縮でトランザクション ログを選らばないと>トランザクション ログの圧縮はできなかったと思います。

なるほど。今までデータベースに対して圧縮をしていました。

まずは教えて頂いたとおり「ディスク使用量」を確認してからトランザクションログファイルの圧縮を実行してみます。

Answer 3

こんにちは、g-yamaga さん。

MSDN フォーラムのご利用ありがとうございます。フォーラム オペレーターの山本です。

その後いかがでしょうか。

M_Lewis さんからの情報は有効な情報であると思われますので、勝手ながら私のほうで回答としてマークさせていただきました。
M_Lewis さん、情報ありがとうございます。
今後、同じ問題でこのスレッドを参照される方にも、有効な情報がわかりやすくなるかと思いますので、参考になった情報や解決に役立った情報には、回答としてマークすることをお願いしています。

よろしければ、お時間のある時にでもその後の状況などお知らせくださいね。
今後とも、MSDN をよろしくお願いいたします。それでは。
                      　　　　　　　　　　　　　　　　　　　       
マイクロソフト株式会社 MSDN フォーラム オペレーター 山本 春海

Answer 4

M_Lewis様、山本春海様

お世話になっております。
結果報告が遅れてしまい申し訳ありません。

先に結論を申しますと、どうやら私の勘違いのようでした。

１．試したこと
テスト用にＤＢを１つ作成し、ミラーリングを設定し、単一テーブルにひたすらインサート＆アップデートを繰り返して、トランザクションログが500MB位になった所で、次の操作を行いました。

（１）１回目にトランザクションログのバックアップを行い、圧縮 → トランザクションログのファイルサイズに変化なし。
（２）２回目にトランザクションログのバックアップを行い、圧縮 →トランザクションログの ファイルサイズが約半分に。
（３）３回目以降、同様に操作して、トランザクションログのファイルサイズがさらに半分以下になっていく。

という結果でした。


２．結果を踏まえて調査したこと
なぜこうなるのかは、以下URLのページを読んで何となく理解できた気がしますが、はっきりとは判りません。

http://msdn.microsoft.com/ja-jp/library/ms179355.aspx


３．結局どうしたか
運用環境では（１）の状態だけを見て圧縮が出来ないと思い込んでしまったようです。

今はトランザクションログを頻繁に取るようにして、圧縮を行っています。
＃それでも1GB程度になっていますが・・・

ここまでで、どこか釈然としないながらも何とか運用に支障の無い状態にはなりましたので、
ご返信頂いた方々に御礼を申しあげたいと思います。

どうもありがとうございました。

今後とも宜しくお願い致します。