みなさん、こんにちは !
「ログファイルが大きくなってディスク領域を圧迫し始めているので、ファイルサイズを小さくしたい」という内容の問合わせは今でも多く寄せられます。今回は、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 を実行する必要があります。明示的な CHECKPOINT の実行方法は、本投稿内の「CHECKPOINT が実行されていない場合」を参照して下さい。
なお、この方法では、トランザクションログを使用したデータベース復旧はできなくなります。そのため、復旧モデルを「単純」に変更する前に、データベースへの書き込みを行わない状態にし、データベースの完全バックアップを取得することをお勧めします。これにより、一連の作業中に不測の事態が発生したとしても、作業開始前の状態までは確実に戻れるようになります。
STEP 3 : トランザクションログファイルのサイズを小さくする
バックアップにより、トランザクションログファイルの中身を消したとしても、ファイル自体のサイズは小さくなりません。
ファイル内に空きがなくなれば、データを書き込むために再びファイルサイズを大きくしなければなりません。当然、ファイルサイズを変更するためには、メモリも CPU も使いますし、ある程度は時間もかかります。そのため、パフォーマンスの観点からは、トランザクションログファイルには、常に空き領域がある状態を保つ方が理想的です。ディスクの空き領域の問題などにより、ファイルを小さくしなければならない場合は、可能な限り小さくするというよりも、ある程度の余裕を持ったサイズにした方がいいでしょう。
手順
データベースを右クリックし、「タスク」、「圧縮」、「ファイル」の順にクリックします。
「ファイルの種類」は「ログ」、「ファイル名」は、大きくなってしまったトランザクションログファイルの論理名、「圧縮アクション」として「未使用領域の解放前にページを再構成する」を選択し、ファイルサイズの目標となるサイズを MB 単位で指定します。このサイズは目標サイズであるため、必ずしも、そのサイズまで小さくできるとは限りません。
この手順を実施してもトランザクションログファイルが小さくならない場合
以上の手順を実施することで、トランザクションログファイルのサイズは小さくできるはずですが、小さくならない場合は、STEP 2, 3 をもう一度繰り返してみて下さい。
もし、それでも小さくならない場合は、そのほとんどは、以下のいずれかが原因です。(これ以外にも原因となるものはありますが、それらは長時間存在し続けるものではないため、通常、上の手順を繰り返し行えば必ずファイルは小さくなります。)
- 実行中のトランザクションがある場合
- トランザクションレプリケーションが構成されていて、まだ配布されていないトランザクションがある場合
- データベースミラーリングが構成されていて、まだ配信されていないトランザクションがある場合
- sync with backup オプションが ON に設定されているディストリビューションデータベースのバックアップが行われていない場合
- CHECKPOINT が実行されていない場合
1. 実行中のトランザクションがある場合
実行中のトランザクションの有無は、GUI ではなく DBCC OPENTRAN コマンドにより確認します。
確認手順
[新しいクエリ] ボタン、もしくは、[ファイル] メニューの [新規作成] – [クエリを現在の接続で実行] から、Management Studio のクエリウィンドウを開き、以下を実行します。
|
DBCC OPENTRAN(‘データベース名’) |
例 : DBCC OPENTRAN('test')
以下は、実行中のトランザクションがない場合の DBCC OPENTRAN 実行結果の例です。この場合、トランザクションログファイルが小さくならない原因は、実行中のトランザクションがあるからではありません。他の原因を確認する必要があります。
|
開かれたアクティブなトランザクションがありません。 |
以下は、実行中のトランザクションがある場合の DBCC OPENTRAN 実行結果の例です。この場合、トランザクションログファイルが小さくならない原因は、実行中のトランザクションがあるからです。トランザクションログファイルを小さくするためには、このトランザクションを終了した後に、STEP 2, 3 を実行する必要があります。
|
データベース 'test' のトランザクション情報。 最も古いアクティブなトランザクション: SPID (サーバー プロセス ID): 51 UID (ユーザー ID) : -1 名前 : user_transaction LSN : (43:2238:2) 開始時刻 : 12 12 2011 7:43:41:553PM SID : 0x0105000000000005150000005d28f57fd53ad8354354e02a481c0000 |
上で確認した SPID を使って以下のクエリを実行すると、このトランザクションを実行しているクライアントプロセスが実行されているマシン名やプログラム名、ログインユーザ名、PID 等を確認することができます。
|
select * from sys.dm_exec_sessions where session_id=上で確認した SPID |
例 : select * from sys.dm_exec_sessions where session_id=51
対処方法
実行中のままになっているトランザクションの実行元マシン、アプリケーション名、PID などを確認できたら、そのアプリケーションがなぜトランザクションをずっと実行中のままなのかを確認しましょう。それが、正常と考えられる状況であれば、そのまま放置し、アプリケーションが処理を完了するのを待ちます。それが異常と考えられる状況であれば、アプリケーションを終了することで、トランザクションも終了させます。
もし、トランザクションを実行しているアプリケーションに対する操作ができないような場合には、以下を実行することで、SQL Server 側からこのトランザクションを強制的に終了させることも可能です。ただし、これを行うと、アプリケーション側ではエラーを受け取り、トランザクションはロールバックされます。
手順
オブジェクトエクスプローラーで対象 SQL Server インスタンスを右クリックし、「利用状況モニター」を起動します。
上で確認した SPID を右クリックし、「強制終了」をクリックします。
もう一度 DBCC OPENTRAN を実行して実行中のトランザクションがなくなっていることを確認後、再度 STEP 2 を実行します。
2. トランザクションレプリケーションが構成されていて、まだ配布されていないトランザクションがある場合
確認手順
[新しいクエリ] ボタン、もしくは、[ファイル] メニューの [新規作成] – [クエリを現在の接続で実行] から、Management Studio のクエリウィンドウを開き、以下を実行します。
|
DBCC OPENTRAN(‘データベース名’) |
例 : DBCC OPENTRAN('test')
以下は、トランザクションレプリケーションの未配布トランザクションがない場合の、DBCC OPENTRAN 実行結果の例です。この場合、トランザクションログファイルが小さくならない原因は、トランザクションレプリケーションの未配布トランザクションがあるからではありません。
|
開かれたアクティブなトランザクションがありません。 |
以下は、トランザクションレプリケーションの未配布トランザクションがある場合の、DBCC OPENTRAN 実行結果の例です。この場合、トランザクションログファイルが小さくならない原因は、トランザクションレプリケーションの未配布トランザクションがあるからです。トランザクションログファイルを小さくするためには、この未配布トランザクションを配布した後に、STEP 2, 3 を実行する必要があります。
|
データベース 'test' のトランザクション情報。 レプリケートされたトランザクション情報: 配布された最も古い LSN : (39:46:1) 配布されなかった最も古い LSN : (39:47:1) |
ここで言う「配布 (distribution/distribute)」とは、ログリーダーエージェントが、パブリケーションデータベースのトランザクションログからトランザクションログを読み取り、読み取ったトランザクションの内容をディストリビューションデータベースに格納することです。
対処方法
配布されていないトランザクションがパブリケーションデータベースにある場合、それは、ログリーダーが正しく動いていない、もしくは、動けていないことが原因です。トランザクションログファイル内の未配布トランザクションを配布してトランザクションログファイルのサイズを小さくするためには、ログリーダーが動かない原因を排除して、レプリケーションを再開することが必要です。
手順
レプリケーションの状態を確認するために、[レプリケーション] を右クリックし、「レプリケーション モニターの起動」を起動して、レプリケーションの状態を確認してみましょう。
レプリケーション モニターでエラーが確認できる場合、「サブスクリプション ウォッチリスト」内の行をダブルクリックして、エラーの詳細が確認できます。
発生している可能性のあるエラーはいろいろとあるので、ここではその解決方法までは伝えられませんが、発生しているエラーの原因を特定して、その原因を排除し、レプリケーションを再開した後に STEP 2, 3 を実行すれば、トランザクションログファイルのサイズを小さくすることができます。
3. データベースミラーリングまたは可用性グループが構成されていて、まだ配信されていないトランザクションがある場合
データベースミラーリングと AlwaysOn 可用性グループもトランザクションレプリケーションと同様にトランザクションログをベースとした機能です。プリンシパルサーバー/プライマリレプリカからミラーサーバー/セカンダリレプリカへのトランザクション配信が行えなくなると、配信が可能になった時に配信を再開できるように、プリンシパルサーバー/プライマリレプリカ上のトランザクションログは、バックアップをしても削除されなくなります。この場合は、配信を再開することが、トランザクションログファイルのサイズを小さくするために必要なことです。
確認手順
データベースミラーリングおよび可用性グループの状態は、Management Studio のオブジェクトエクスプローラーで確認することができます。以下は、データベースミラーリングの場合の例です。
上の画像のように、「同期済み」となっている場合には、トランザクションログファイルが小さくならない原因は、ミラーリングや可用性グループではありません。
それ以外の場合、ミラーリングや可用性グループが原因である可能性があります。
対処方法
「同期中」である場合は、しばらく待った後、再度ミラーリングの状態を確認して下さい。
「接続解除」である場合は、ミラーサーバーが起動しているかどうかを確認して下さい。起動していない場合は、起動して下さい。 起動している状態でも「接続解除」になっている場合は、ping により、ネットワーク接続に問題がないかどうかを確認して下さい。ping が通る場合は、プリンシパルサーバー上で Management Studio や sqlcmd.exe から、ミラーサーバーとなっている SQL Server へ接続できるかどうか確認して下さい。これらのテストに失敗する場合は、名前解決を含むネットワークの問題やファイアウォールの設定の問題である可能性があるため、ネットワークやファイアウォールの設定を見直して下さい。
プリンシパル上の Management Studio や sqlcmd を用いてミラーサーバーに接続できる場合、プリンシパルサーバー上の SQL Server ERRORLOG (SQL Server 2019 既定のインスタンスの場合 C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log 下の ERRORLOG ファイル) を参照し、エラーの原因を突き止める必要があります。
ミラーリングの状態が「同期済み」となれば、STEP 2, 3 を実行することで、トランザクションログファイルのサイズを小さくすることができます。
4. sync with backup オプションが ON に設定されているディストリビューションデータベースのバックアップが行われていない場合
ディストリビューションデータベースの sync with backup オプションが有効になっている場合、トランザクションレプリケーションのパブリケーションデータベースでは、トランザクションログバックアップを行ったとしても、ディストリビューションデータベースのフルデータベースバックアップが完了するまではトランザクションログは切り捨てられません。
対処方法
ディストリビューションデータベースのフルデータベースバックアップを行います。ディストリビューションデータベースのフルデータベースバックアップ完了後、パブリケーションデータベースのトランザクションログバックアップを行います。
< 参考情報 >
スナップショット レプリケーションおよびトランザクション レプリケーションのバックアップと復元の方式
https://docs.microsoft.com/ja-jp/sql/relational-databases/replication/administration/strategies-for-backing-up-and-restoring-snapshot-and-transactional-replication?view=sql-server-ver15
5. CHECKPOINT が実行されていない場合
確認手順
これが原因になることはほとんどありませんが、復旧モデルが「単純」で上の STEP 2, 3 を実行してもトランザクションログファイルサイズが小さくならない場合は、CHECKPOINT が実行されていないために、トランザクションログが切り捨てられていない可能性があります。
この状況に該当しているかどうかを判断するためには、sys.databases カタログビューの log_reuse_wait_desc を確認します。CHECKPOINT または XTP_CHECKPOINT となっている場合には、該当しています。
|
select name, log_reuse_wait_desc from sys.databases |
なお、インメモリ OLTP (メモリ最適化ファイルグループ) 機能を使用している場合には、パフォーマンスの観点から、最後の CHECKPOINT 以降に生成されたトランザクションログが 1.5GB 以上になるまでは自動的には CHECKPOINT は実行されないようになっています。そのため、インメモリ OLTP を使用していないデータベースに比べて、トランザクションログファイルの使用量が多くなる場合があります。この動作に起因して CHECKPOINT が自動的に実行されていない場合であっても、以下の方法で明示的に CHECKPOINT を実行すれば、トランザクションログは切り捨てられます。
対処方法
Management Studio のクエリウィンドウを開き、トランザクションログファイルのサイズを小さくしたいデータベースに変更した後に、CHECKPOINT コマンドを実行して下さい。
以下の例は、test データベースのトランザクションログファイルを小さくしたい場合に実行する CHECKPOINT コマンドの実行例です。
対象データベースを選択し、そのデータベース上で実行することがポイントです。
その後、STEP 3 を再実行して下さい。
Posted at https://sl.advdat.com/3zSbs5S