SQL Server のトランザクションログのファイルを小さくする方法
〜.ldf のファイルが、大きくなってしまうときの、原因とか対応方法のまとめ。
原因を突き止め、トランザクションログを切り捨てたり、圧縮したりして、サーバの容量を確保しましょう。
原因
トランザクションログ（単純にログと呼ばれたり、ジャーナルと呼ばれたりするもの）が大きくなるということは、次のことが考えられると思います。
- 復旧モデルが「完全」か「一括ログ」になっている
- 管理のメンテナンスプランなどで、「トランザクションログのバックアップ」をとっていない
そのほかにも、トランザクションの実行時間が長かったり、ミラーリングの構成でも一時的に大きくなるようです。
今まで経験した中で、一番多いのが、２番目に上げた「トランザクションログのバックアップ」をとっていない状況ですね。
「完全」バックアップ(2008/2005)もしくは、「データベース全体のバックアップ」(2000)のみを行っていて、トランザクションログのバックアップを行っていない場合がほとんど。
Oracleを使われていた方が多いのか、ダンプを取れば、後は何もしない的な考えでしょうか・・・。
調査
SQL Server 2000 の調べ方
一概には判断できませんが、一応調べ方を。
対象のＤＢを選択し、タスクパッドを見てみましょう。下図が表示されないときは、メニューバーの「表示」−「タスク パッド」を選択。
見る場所は、「トランザクション ログ領域」です。
「使用中」が大きいときは、トランザクションログのバックアップをとってみましょう。
「空き」が大きいときは、圧縮や、空き領域の切り詰めを行ってみましょう。
SQL Server 2005 や 2008 の調べ方
トランザクションログが切り捨てられない原因を、次のSQLで確認できます。
USE master; SELECT name ,log_reuse_wait ,log_reuse_wait_desc FROM sys.databases ;
log_reuse_wait_desc が、LOG_BACKUP となっている場合は、ログの先頭を前方に移動するためにログ バックアップが必要であることを表しています。
- MSDN ログの切り捨てが遅れる原因となる要因
対応方法
トランザクションログのバックアップを定期的にとる
（運用していて、初めてバックアップをとる場合は、〜.ldf と同じくらいの容量がバックアップファイルとして作成されるので、容量を確認！）
メンテナンスプランに、トランザクションログのバックアップを設定しましょう。
2000の「新規保守計画(P)...」からの「データベース保守計画ウィザード」なら、下図の赤枠をチェック
2008とかの「メンテナンスプラン ウィザード」なら、下図の赤枠にチェック
保存場所や、スケジューリングは、ウィザードに従って進めてください。
トランザクションログの圧縮と、空き領域の開放
トランザクションログのバックアップをとると、〜.ldf ファイルは小さくなるはずですが、それでも大きい場合は、圧縮してみます。
また、データは小さく圧縮されても、ファイル領域は確保されたままになることもあります。その場合は、未使用領域を開放させます。
- SQL Server 2000(Enterprise Manager)の場合
対象のＤＢを右クリックし、「すべてのタスク」−「データベースの圧縮」を選択。
「ファイル」ボタンをクリック。
「データベースファイル」をトランザクションログのファイルに選択しなおし、圧縮操作などを設定。「ＯＫ」をクリックすると、圧縮が始まります。
終わりました。
- SQL Server 2005(Management Studio)の場合
対象のＤＢを右クリックし、「タスク」−「圧縮」−「ファイル」を選択。
ファイルの種類で「ログ」を選択し、圧縮したいログファイルを選択。「ＯＫ」をクリックすると、圧縮が開始されます。
（都合により、あちこちマスキングしています）
- SQLコマンド
圧縮するのに、DBCC SHRINKFILE が使えそうです。
詳細は後日・・・
バックアップファイルの削除
定期的にバックアップをとると、当たり前ですが、バックアップファイルが増えていきます。
メンテナンスプランで消せますが、うまく消えないときがあります。
特定のフォルダ配下のファイルを、指定期間分残して、他は削除するバッチを以下で公開しています。
