昨日、twitter でつぶやきに上がっていたので軽くまとめてみたいと思います。
SQL Server 2005 までは、 [BACKUP LOG WITH TRUNCATE_ONLY] を使用することで、トランザクションログの強制的な切り捨てをすることができました。
SQL Server 2008 以降では、この SQL は使用できなくなっています。
SQL Server 2008 R2 で廃止されたデータベース エンジンの機能
SQL Server 2008 以降でトランザクションログの強制的な切り捨てはどのように行うかというのが今回の投稿になります。
■復旧モデルの変更
これは、上記のリンクに書かれている内容になります。
なし。データベースに単純復旧モデルが使用されている場合は、トランザクション ログの切り捨てが自動的に行われます。 |
トランザクションログを手動で強制的に切り捨ての必要が発生する可能性があるのは、復旧モデルが [完全] [一括ログ] を使用していて、定期的にトランザクションログのバックアップを取得しておらず、トランザクションログの領域が圧迫してしまった場合だと思います。
トランザクションログのバックアップを取得すれば解決するのですが、ディスク領域が枯渇しており、バックアップの取得先がない場合は強制的にログを切り捨てる必要があります。
# SQL Server のサービスアカウントがアクセスできる共有フォルダがあれば、ネットワーク越しに取得できることもありますが。
このような場合は復旧モデルを [単純] に変更して、トランザクションログを切り捨てることができます。
復旧モデルが単純の場合はチェックポイント発生時にすでに確定済みのログレコードが自動で切り捨てられます。
それでは、実際に動作を確認してみたいと思います。
[DBCC SQLPERF(‘LOGSPACE’)] を使用して、トランザクションログの使用状況を確認します。
今回は [TEST] というデータベースを使用しているのですが、300 MB のサイズで 88% を使用していることが確認できます。
それでは、[ALTER DATABASE [TEST] SET RECOVERY SIMPLE] を実行して、その後ログのサイズを確認してみます。
ログの使用量が 0.8% になっていますね。
復旧モデルを変更することでログが切り捨てられたことが確認できました。
■NULL デバイスにバックアップを取得
復旧モデルを変更したくないという場合にはバックアップを NULL デバイスに対して取得することでログを切り捨てることも可能です。
ただし、この場合にはバックアップの関連性が崩れてしまいますので、通常取得しているバックアップのリストアに影響が出る可能性がありますのでご注意いただければと思います。
NULL デバイスへのログバックアップは以下のように実行します。
BACKUP LOG [TEST] TO DISK = N’NUL’ |
今回は [NUL] としていますが [NUL:] でも取得できます。
# NULL としてしまうと既定のバックアップディレクトリに NULL というファイル名でバックアップが取得されるので注意ください。
NULL デバイスに取得した場合は実際にはバックアップファイルは出力されません。
# NULL デバイスへの出力のため
ただし、トランザクションログの切り捨てに関しては通常のバックアップを取得したものと同じで行われますので、ログの切り捨てをするために使用できます。
この場合、トランザクションログのバックアップの連続性は切れてしまうことになりますので、実際に運用時に使用する場合には注意が必要です。
また、トランザクションログのバックアップはデータベースの完全バックアップを取得している必要があります。
データベースの完全バックアップを取得していない場合は、以下のエラーとなりログのバックアップを取得することができません。
すでにバックアップを取得したデータベースで試したい場合は、復旧モデルを単純にし、その後復旧モデルを完全か一括ログにすると以下のエラーを発生させることができます。
NULL デバイスへのバックアップの取得は BACKUP DATABASE でも実行できますので、以下のように実行するとデータベースのバックアップとログのバックアップを NULL デバイスに取得できます。
BACKUP DATABASE [TEST] TO DISK = N’NUL’ |
どちらのバックアップも NULL デバイスという出力先に取得していることになりますので、実態はありませんがバックアップとしては取得されていることになります。
NULL デバイスにバックアップを取得する場合はバックアップの連続性に注意する必要がありますね。
手動のログの切り捨ては発生しないように設計するのが重要となりますが、実施する必要が出た場合のメモとして残しておきたいと思います。
SQL Server 2008 以降のログの切り捨て ≪ SE の雑記…
素敵なエントリーの登録ありがとうございます – .NET Clipsからのトラックバック…
.NET Clips
30 6月 11 at 00:22
[…] (参考) http://engineermemo.wordpress.com/2011/06/29/sql-server-2008-%E4%BB%A5%E9%99%8D%E3%81%AE%E3%83%AD%E3… […]
[SQL Server] バックアップファイルを出力せずにトランザクションログを切り捨てる| Lazyfiles
28 10月 13 at 21:55