みなさん、こんにちは !
「ログファイルが大きくなってディスク領域を圧迫し始めているので、ファイルサイズを小さくしたい」という内容の問合わせは今でも多く寄せられます。今回は、SQL Server Management Studio GUI を使って、トランザクションログファイルのサイズを小さくする手順を紹介します。
STEP 1 : データベースの復旧モデルを確認する
STEP 2 : トランザクションログをバックアップする
STEP 3 : トランザクションログファイルのサイズを小さくする
※復旧モデルが「単純」の場合、もしくは「完全」や「一括ログ」でデータベースの完全バックアップが一度も取得されていない場合、STEP 2 をスキップします。
以上の手順で、トランザクションログファイルのサイズを小さくしたいという状況のほとんどに対応可能だと思います。
ここに記載した方法でトランザクションログファイルのサイズを小さくできない場合は、おそらく、トランザクションログファイルのサイズを小さくする前に、レプリケーションやミラーリングのトラブルシューティングなどが必要になるでしょう。
STEP 1 : データベースの復旧モデルを確認する
復旧モデルが「単純」かそれ以外かによって、以降の手順が違ってきますので、まず最初に、データベースの復旧モデルを確認します。
手順
復旧モデルを確認するために、データベースのプロパティを表示します。データベースのプロパティは、オブジェクトエクスプローラーでデータベース名を右クリックし、「プロパティ」をクリックすることで表示できます。
表示されたダイアログボックスの左ペインで「オプション」を選択すると、右ペインに「復旧モデル」が表示されます。復旧モデルは、「単純」「完全」「一括ログ」のいずれかです。
STEP 2 : トランザクションログをバックアップする
トランザクションログは、データベースファイルへの更新履歴ですので、データベースに対して更新を行うたびにトランザクションログファイルには履歴データが記録され、何もしなければ、トランザクションログファイル内の履歴データはどんどん増えていきます。
復旧モデルが「単純」の場合は、トランザクションログファイル内のデータ量が一定量を超えると、SQL Server がファイルの中身を消し、ファイル内に空き領域を作り、空いた領域は再利用されます。
一方、復旧モデルが「完全」または「一括ログ」に設定されている場合は、過去に一度でもデータベースの完全バックアップ (データベースフルバックアップ) を取得していると、SQL Server はファイルの中身を消すことはしませんので、トランザクションログファイル内のデータは、バックアップしなければ削除されません。
この STEP 2 は、復旧モデルが「完全」または「一括ログ」の場合のみ行います。「単純」の場合は、次の STEP 3 に進みます。
手順
データベースを右クリックし、「タスク」 – 「バックアップ」をクリックします。
「バックアップの種類」として「トランザクションログ」を選びます。もし、復旧モデルが「単純」に設定されている場合は、「トランザクションログ」は選択できません。
「バックアップセットの有効期限」は既定のまま、「バックアップ先」は、バックアップデータを書き込むファイル名を指定します。「ディスク」を指定して、「追加」でファイル名を指定して下さい。そのサーバーにテープデバイスがある場合には、「テープ」を選んでも構いません。
最後に「OK」を押すと、バックアップが開始されます。
復旧モデルが「完全」または「一括ログ」であっても、データベースの完全バックアップが一度も取得されていない場合、トランザクションログのバックアップは以下のエラーで失敗します。
メッセージ 4214、レベル 16、状態 1 現在、データベースのバックアップが存在しないので、BACKUP LOG を実行できません。
復旧モデルが「完全」または「一括ログ」であっても、データベースの完全バックアップが一度も取得されていない場合は、トランザクションログは自動的に切り捨てられていますので、このステップを実行せずに STEP 3 に進むことができます。
! : バックアップファイルの出力先にバックアップデータを保持できるだけの十分な容量がない場合
バックアップファイルの出力先にすべてのバックアップデータを保持できるだけの容量がない場合、バックアップは失敗します。この場合、トランザクションログの切り捨ては行われません。バックアップファイルの出力先に十分な容量が確保できない場合は、データベースの復旧モデルを一時的に「完全」や「一括ログ」から「単純」に変更することで、STEP 3 を行うことができるようになります。
STEP 3 に進む前に CHECKPOINT が実行される必要がありますので、復旧モデル変更後は、データベースに対して CHECKPOINT が実行されるだけの量の更新が行われるのをしばらく待つか、明示的に CHECKPOINT を実行する必要があります。明示的な