SQL Server/トランザクションログファイル

• トランザクション ログ ファイルのサイズの管理
• 【SQLServer】知ってた？トランザクションログバックアップが、ログ削除の方法です。

• HowTo: Management Studio を使ってトランザクションログファイル (ldf) のサイズを小さくする方法

• 満杯になったトランザクション ログのトラブルシューティング (SQL Server エラー 9002)
• SQL Server データベースのいっぱいになったトランザクション ログからの回復

• トランザクションログを手動で強制的に切り捨ての必要が発生する可能性があるのは、復旧モデルが [完全] [一括ログ] を使用していて、定期的にトランザクションログのバックアップを取得しておらず、トランザクションログの領域が圧迫してしまった場合

• トランザクション ログがいっぱいになった場合は、トランザクション ログ ファイルのバックアップを直ちに作成してください。
• トランザクション ログ ファイルのバックアップの作成中に、トランザクション ログのアクティブでない部分が SQL Server により自動的に切り捨てられます。
• トランザクション ログ ファイルのアクティブでない部分には、完了したトランザクションが含まれます。そのため、そのトランザクション ログ ファイルが以降の復旧処理で SQL Server によって使用されることはありません。
• トランザクション ログの拡張でさらに多くの領域を使用できるようにするのではなく、トランザクション ログ内の切り捨てられたアクティブでない領域を再利用します。

• Truncate メソッドを使用して、トランザクション ログ ファイルからアクティブでないトランザクションを削除できます。

• 1日に1回再起動するシステムでトランザクションログファイルが肥大化した（ファイル管理トラブル）
  • 復旧モデルは、規定では完全
  • 完全復旧モデルのデータベースは、一度完全バックアップを取得すると、その後トランザクションログはトランザクションログのバックアップをしない限り履歴を記録し続ける
  • トランザクションログのバックアップを行うとファイルは小さくなる
  • 一度でも完全バックアップを行ったならば、以後はトランザクションログをバックアップする運用が必要になる

• SQL Serverのトランザクションログを圧縮する (SQL Server Tips)

トランザクションログファイル †

トランザクションログとは †

• 復旧時は、まず完全バックアップをリカバリし、その後、トランザクションログを反映させることにより、データベースをほぼ最新の状態に戻すことが可能
• 完全復旧モデルを成り立たせる根幹の仕組み

トランザクションログの切捨て †

• 単純復旧モデルでは勝手に切り捨てられる
• 完全復旧モデルでは、以下の条件を満たしたときに切り捨てられる
  • トランザクションログをバックアップしたとき
  • かつ、チェックポイントされていること

• 一度肥大化してしまったトランザクションログの物理ファイルサイズは、ログを切り捨てても小さくならない
• 別途、トランザクションログを圧縮する処理が必要
• 圧縮しなくても、切り捨てで空いた領域は再利用されるので、定期的にログ切り捨てを行っていけば肥大化を抑えることはできる

チェックポイント †

• チェックポイントとは、変更情報をデータベースファイルに反映させる処理
• デフォルトでは1分間隔ぐらいで行われている

• チェックポイントには自動的に発生するものと、手動操作により発生するものがある
• 手動操作の主要なものでは、データベースのバックアップ時、再起動時
• 自動では、以下のどちらかのタイミング
  • ログが全体の70%に達したとき
  • 復旧間隔] オプションで指定された時間内に処理できるとデータベース エンジンが推定したログ レコード数に達したとき

圧縮 †

• SQL Serverのトランザクションログを圧縮する (SQL Server Tips)
• SQL Serverのトランザクションログを消す（クリア）する方法
• HowTo: Management Studio を使ってトランザクションログファイル (ldf) のサイズを小さくする方法

コマンド †

• BACKUP (Transact-SQL)

BACKUP LOG [DB名] TO DISK=[バックアップファイル出力先] WITH INIT, COMPRESSION

• INIT
  • すべてのバックアップ セットが上書きされるように指定します。ただし、メディア ヘッダーは維持されます。
  • INIT が指定された場合、条件が満たされていれば、そのデバイス上にある既存のすべてのバックアップ セットが上書きされます。
  • 既定では、BACKUP によって次の状況が確認され、いずれかの状況に該当する場合はバックアップ メディアは上書きされません。

• COMPRESSION
  • バックアップの圧縮を明示的に有効にします。

ログファイル縮小方法 †

縮小化 †

復旧モデルを変更せずに縮小化する方法

• DBバックアップ
  • バックアップの種類：完全で バックアップ
  • バックアップの種類：トランザクションログ でバックアップ

• SQL Server Management Studioで以下の操作を実行
  • オブジェクトエクスプローラから、該当のDBをを右クリック
  • [タスク][圧縮]→[ファイル]を選択
  • [ファイルの圧縮]画面が開く
  • ファイルの種類を「ログ」に変更
  • 圧縮アクションを「未使用領域の解放前にページを再構成する」に変更し、ファイルサイズを指定（あくまで目標値なので、とりあえず2MBと指定）
  • 圧縮を実行

• 一度で小さくならないことがあるので、その場合はDBバックアップから再実行

強制切り捨て †

• SQL Server 2008 以降のログの切り捨て

• 復旧モデルを「単純復旧モデル」に変更する
  • [DBCC SQLPERF(‘LOGSPACE’)] を使用して、トランザクションログの使用状況を確認
  • [ALTER DATABASE [DB名] SET RECOVERY SIMPLE] を実行
  • [DBCC SQLPERF(‘LOGSPACE’)] を使用して、トランザクションログの使用状況が減ったのを確認
  • SQL Server Management Studioで以下の操作を実行
    • オブジェクトエクスプローラから、該当のDBをを右クリック
    • [タスク][圧縮]→[ファイル]を選択
    • [ファイルの圧縮]画面が開く
    • ファイルの種類を「ログ」に変更
    • 圧縮を実行

LSN †

• LSN とは？