SQLServerのトランザクションログ管理の方法

ログファイルをサイズ上限を無制限にしておくと、どんどんサイズが大きくなってしまいますよね。かといってサイズの上限を決めておくと、そこに達したらそれ以上データを作成できなくなってしまいます。

ある程度のサイズになったら、または週に一度くらいで自動的にトランザクションログを切り捨てたいんですが、どのような方法がありますでしょうか？
既にかなりのサイズになっており、困ってます。

本にはログのバックアップを取ると良いとあったのですが、バックアップを取るということは領域の解放、ログファイルサイズの縮小にはならないんじゃないかと思い、有識者のみなさまに質問させていただくことにしました。みなさまおすすめの管理方法を教えてください。

P.S.
やはり、物理的にファイルを削除するのは難がありますでしょうか？

No.1 ベストアンサー 回答者： matu_tak 回答日時： 2005/10/27 00:06

>本にはログのバックアップを取ると良いとあったのですが、

>バックアップを取るということは領域の解放、
>ログファイルサイズの縮小にはならないんじゃないかと思い、
>有識者のみなさまに
>
うーーむ、まるで本の著者が有識者ではないような表現ですね...
ちなみに、私は有識者ではありませんが。

その本に書いてあるとおり、ログの最適な管理方法は
定期的にログをバックアップすることです。

> ログファイルサイズの縮小にはならないんじゃないかと思い
>
ログファイルを縮小することは必ずしもいいこととは限りません。
仮に、ログファイルを小さくしても、大きいトランザクションが
実行されれば、ログは自動拡張するわけです。この自動拡張がくせもの
で、拡張はデフォルトで 10％ ずつなので、頻繁な拡張が発生すると、
無茶苦茶おそくなります。
なので、最大トランザクションに耐えられるよう、
あらかじめログを大きめにとっておくというのは、パフォーマンス上
やっておいたほうがよいことになります。

どうしても、小さく保っておきたい場合は、

・復旧モデルを「シンプル」

にし、かつデータベースのオプションで「自動圧縮」をチェックして
おきます。パフォーマンス上の弊害は前述のとおりです。
また、復旧モデルがシンプルの場合の弊害は、以下の堀川さんの記事が
参考になると思います。

特集： バックアップとリカバリの克服
http://www.sqlpassj.org/tokushu/backup/default.a …

また、どうしてもログを手動で縮小したい場合は、
Enterprise Manager で「すべてのタスク」メニューから「データベースの圧縮」
または
DBCC SHRINKFILE コマンドを使います。
以下も参考になると思います。

Re: トランザクションログの切捨て不能-2
http://www.sqlpassj.org/bbs/ml_disp.aspx?forum_i …


松本崇博
Blog: http://blogs.sqlpassj.org/matu_tak/

この回答へのお礼

アドバイスありがとうございます。本の著者の方が有識者ではないとは思っておりません。たぶん、文章の裏に根拠があるのだろう（もしくは私の読み違い）と思ったのですが、如何せん、私の今の知識ではそれがわからなかったため、有識者でご存知の方がいらっしゃれば、それを指摘してもらえるかもと思ったものです。
質問してからさらに調べたのですが、バックアップを取るときに最新のログのみ残して確定済のログを消すことでログファイルの増大を防ぎ、本当にログファイルを切捨て（縮小）するのは別のコマンドが必要のようですね。トランザクションログに関する文章をもっと読む必要を感じておりましたので、いろいろなページを紹介していただき、助かりました。参考のページを拝見してみます。

お礼日時：2005/10/27 06:33

No.2 回答者： noname#17136 回答日時： 2005/10/27 02:46

ログの切り捨ては蓄積した変更履歴を放棄することのため

切捨て後は直ぐにデータベースのフルバックアップを取るようにします。
※自動復旧でロールフォワードされないため（変更履歴が反映されない）

BACKUP TRAN WITH NO_LOG

とかで一発切り捨てもできたような。
※誤字があるかも

＞＞物理的にファイルを削除

これはサーバが起動しなくなるような。

この回答へのお礼

回答ありがとうございます。データベースは500MB程度なのにログが既に数GBに達していたため、かなり困っておりました。コマンド試してみます。物理的にファイルを削除は前任者の方がやられていたようで、なぜか無事だったようです。とはいえ今後データベースを預かる身としてはそれを続けていくのはかなり怖いので、きちんとしたコマンドを探しておりました。

お礼日時：2005/10/27 06:23

No.3 回答者： khazad-lefty 回答日時： 2005/10/27 10:19

> バックアップを取るということは領域の解放、ログファイルサイズの縮小にはならないんじゃないか

逆に、バックアップを行わないと解放は行われません。
その本にはバックアップのコマンドとかは書いてないのでしょうか？

とりあえずBooks Onlineで、Backup Logの項目を調べてみるといいのでは？

この回答へのお礼

アドバイスありがとうございます。BACKUPコマンドがあったので実行してみたのですが、物理ファイルのサイズを小さくするものではありませんでした。いろいろ調べた結果、DBCC SHRINKFILEをジョブで実行して定期的にサイズを小さくするのが一番希望に近いようなのですが、イレギュラーな方法をとっているのではないかと不安です。

お礼日時：2005/10/27 18:01