[解決済み] SQL Serverのトランザクションログをクリアするにはどうすればよいですか？

2022-03-15 01:22:18

質問

私はSQLの専門家ではないので、基本以上のことをする必要があるたびに、その事実を思い知らされます。私はテストデータベースを持っていて、サイズは大きくないのですが、トランザクションログは確実に大きくなっています。トランザクション・ログを消去するにはどうすればよいのでしょうか？

どのように解決するのですか？

ログファイルを小さくするのは、予期しない増大が起こり、それが再び起こるとは思わないシナリオのために確保されるべきです。もしログファイルが再び同じサイズに成長するならば、一時的に縮小してもあまり意味がありません。さて、データベースの復旧目標に応じて、取るべき行動は次のとおりです。

まず、フルバックアップを取る

何か問題が発生したときに復元できることを確認せずに、データベースを変更することは絶対にしないでください。

ポイントインタイムリカバリーにこだわるなら

(ポイントインタイムリカバリというのは、フルバックアップや差分バックアップ以外へのリストアが可能かどうかという意味です)

おそらく、あなたのデータベースは FULL リカバリーモードそうでない場合は、リカバリーモードであることを確認してください。

ALTER DATABASE testdb SET RECOVERY FULL;

定期的にフルバックアップを行っていても、ログファイルはログこれは、ディスク容量を無駄に消費させるためではなく、お客様を保護するためのものです。このログバックアップは、リカバリーの目的に応じて、かなり頻繁に実行する必要があります。例えば、災害時に15分以上のデータを失わないというビジネスルールがある場合、15分ごとにログをバックアップするジョブが必要です。ここでは、現在時刻に基づいてタイムスタンプ付きのファイル名を生成するスクリプトを紹介します（ただし、メンテナンスプランなどでも可能です。ただし、メンテナンスプランの縮小オプションは選択しないでください、ひどいものです）。

DECLARE @path NVARCHAR(255) = N'\\backup_share\log\testdb_' 
  + CONVERT(CHAR(8), GETDATE(), 112) + '_'
  + REPLACE(CONVERT(CHAR(8), GETDATE(), 108),':','')
  + '.trn';

BACKUP LOG foo TO DISK = @path WITH INIT, COMPRESSION;

なお \\backup_share\ は、異なる基礎となるストレージデバイスを表す別のマシンにあるべきものです。これらを同じマシン（または同じディスクを使用する別のマシン、あるいは同じ物理ホスト上の別のVM）にバックアップしても、マシンが爆発したらデータベースを失ってしまうので、実際には役に立ちません。とのバックアップが必要です。ネットワークインフラによっては、ローカルにバックアップを取り、裏で別の場所に転送する方が理にかなっている場合もあります。

さて、定期的なログバックアップを実行したら、ログファイルを今よりもっと合理的な大きさに縮小するのが妥当でしょう。そのためには ではなく を実行するということです。 SHRINKFILE ログファイルが1MBになるまで、何度も何度も繰り返すことになります。たとえログを頻繁にバックアップしていたとしても、同時に発生しうるトランザクションの合計を収容する必要があります。ログファイルの自動増加イベントは、SQL Server がファイルをゼロにする必要があり（ファイルの即時初期化が有効なデータファイルとは異なります）、ユーザートランザクションはこれが発生する間待機しなければならないため、コストがかかります。このgrow-shrink-grow-shrinkルーチンをできるだけ少なくしたいし、ユーザーにその代償を払わせたくはないでしょう。

なお、シュリンクが可能になる前に、ログのバックアップが2回必要になる場合があります（Robertに感謝）。

ですから、ログファイルの実用的なサイズを考える必要があります。しかし、ログファイルを頻繁に縮小し、それがまた大きくなっている場合、良い透かしは、おそらく最大のものよりも10～50％高いものでしょう。例えば、ログファイルが200MBになり、それ以降に発生する自動増殖イベントを50MBにしたい場合、この方法でログファイルのサイズを調整することができます。

USE [master];
GO
ALTER DATABASE Test1 
  MODIFY FILE
  (NAME = yourdb_log, SIZE = 200MB, FILEGROWTH = 50MB);
GO

なお、現在ログファイルが > 200 MB の場合、最初にこれを実行する必要があるかもしれません。

USE yourdb;
GO
DBCC SHRINKFILE(yourdb_log, 200);
GO

ポイントインタイムリカバリーにこだわらない場合

もし、これがテスト用のデータベースで、ポイントインタイムリカバリーにこだわらないのであれば、データベースを SIMPLE リカバリーモード

ALTER DATABASE testdb SET RECOVERY SIMPLE;

データベースを置く SIMPLE リカバリーモードでは、SQL Server がログファイルの一部を再利用するようにします (基本的に非アクティブなトランザクションを段階的に削除します)。 すべて のような）トランザクションが発生します。 FULL のリカバリは、ログをバックアップするまで行われます)。 CHECKPOINT イベントは、ログを制御するのに役立ちます。 CHECKPOINT s.

次に、このログの増加が、通常の日常的な使用によるものではなく、本当に異常な出来事（例えば、年に一度のスプリング・クリーニングや最大のインデックスの再構築）によるものであるかどうかを絶対に確認する必要があります。もし、ログファイルを非常に小さなサイズに縮小し、SQL Serverが通常のアクティビティに対応するために再びログファイルを大きくしなければならないとしたら、何を得ることができたのでしょうか？一時的に解放されたディスクスペースを活用することができたのでしょうか？もし、すぐに修正する必要があるのなら、次のように実行してください。

USE yourdb;
GO
CHECKPOINT;
GO
CHECKPOINT; -- run twice to ensure file wrap-around
GO
DBCC SHRINKFILE(yourdb_log, 200); -- unit is set in MBs
GO

それ以外の場合は、適切なサイズと成長率を設定します。ポイントインタイムリカバリーの場合の例と同様に、同じコードとロジックを使用して、どのファイルサイズが適切かを判断し、妥当な自動成長パラメータを設定することができます。

やってはいけないこと

でログをバックアップします。 TRUNCATE_ONLY オプションを指定し SHRINKFILE . ひとつは、この TRUNCATE_ONLY オプションは非推奨となり、現在のバージョンの SQL Server では使用できなくなりました。第二に、もしあなたが FULL リカバリーモデルでは、ログチェーンが破壊され、新しい完全なバックアップが必要になります。
データベースを切り離し、ログファイルを削除し、再度アタッチします。 . これがどれほど危険なことなのか、私は強調することができません。データベースが復旧しないかもしれないし、怪しいと思われるかもしれないし、（バックアップがあれば）元に戻さなければならないかもしれない。
shrink database"オプションを使用します。 . DBCC SHRINKDATABASE とメンテナンスプランのオプションで同じことをするのは、特にログの問題を解決する必要があるだけなら、悪い考えです。調整したいファイルをターゲットにして、それを独立して調整するには DBCC SHRINKFILE または ALTER DATABASE ... MODIFY FILE (上記の例)。
ログファイルを1MBに縮小 . なぜなら、SQL Server は特定のシナリオでそれをさせてくれるし、それによって解放されるすべてのスペースを見てください！これは魅力的に見えます。データベースが読み取り専用でなければ（読み取り専用である場合は ALTER DATABASE ログはリカバリーモデルに関係なく、現在のトランザクションに対応しなければならないからです。一時的にスペースを解放して、SQL Serverがゆっくり、苦労してそれを取り戻すことに何の意味があるのでしょうか？
2つ目のログファイルを作成する . これは、ディスクをいっぱいにしてしまったドライブを一時的に救済するものですが、これはパンクした肺をバンドエイドで治そうとするようなものです。別の潜在的な問題を追加するのではなく、問題のあるログファイルを直接処理する必要があります。トランザクションログの活動を別のドライブにリダイレクトする以外に、2つ目のログファイルは（2つ目のデータファイルと違って）何の役にも立たないのです。ポール・ランダルは、なぜ複数のログファイルが後々あなたを苦しめることになるのかについても説明しています。 .

積極的に行動する

ログファイルを小さくして、常に小さな割合で自動成長させるのではなく、ある程度大きなサイズ（同時実行トランザクションの最大セットの合計を収容できるサイズ）に設定し、予備として適切な自動成長設定を行うことで、単一のトランザクションを満たすために複数回成長する必要がなく、通常のビジネス運用中に成長する必要が比較的少なくなるようにするのです。

ここで考えられる最悪の設定は、1MB増量または10%増量です。面白いことに、これらはSQL Serverのデフォルトです（私が文句を言ったことがありを変更するよう依頼したが、無駄だったデータファイルは1MB、ログファイルは10％）。前者は今の時代では小さすぎるし、後者は毎回イベントが長くなってしまう（例えば、ログファイルが500MBで、最初の伸びが50MB、次の伸びが55MB、次の伸びが60.5MB、などなど。- そして、低速のI/Oでは、私を信じて、あなたは本当にこの曲線に気づくでしょう）。