SQL ServerデータベースでCDCが開かれたために、トランザクションログスペースがいっぱいになった理由


SQL ServerでCDCを有効にすると、場合によっては、トランザクションログスペースが次のようにいっぱいになります。
データベース'***'のトランザクションログは'REPLICATION'のためにいっぱいです(データベース'***'のトランザクションログは"REPLICATION"のためにいっぱいです)。
CDCとレプリケーションの基本原則大まかに言えば、ログを使用するための手順は次のとおりです。
1.基になるテーブル(CDCまたはレプリケーションがオンになっているテーブル)がトランザクション操作(追加、削除、および変更)を生成するたびに、対応するトランザクションログがログファイルに書き込まれます。
2.この時点でのログは、状態によってレプリケーションとしてマークされています。つまり、ログはレプリケートされる状態にあります。このアクティブな状態は、単純な復元モードであっても、データベースの復元モードとは関係ありません。
3、次に、ログを読み取り、トランザクションログのメモリに従ってターゲットテーブルを書き込むバックグラウンドプロセスがあります。
このターゲットはシステムですcdcのデータ変更を記録するmテーブル、
レプリケーションの場合は、配布ライブラリを作成します
4.手順3が完了すると、トランザクションログは通常としてマークされます。単純な復元モードの場合、バックグラウンドプロセスによって解析されたトランザクションログは切り捨てられ、再利用できます。上記の3番目のステップで問題が発生した場合、つまり、バックグラウンドプロセスがログを解析できなかった後、使用可能なログスペースが解放され、データベースへの書き込み操作が表示されます。データベース「TestDB」のトランザクションログがいっぱいです。 、理由は「REPLICATION」です。
この記事では、CDCをオンにするとログ領域がいっぱいになる現象と、対応する解決策について説明します。
テスト環境のセットアップ span>
まず、テストデータベースを作成します。


USE master
GO
CREATE DATABASE TestLogFull ON PRIMARY

NAME = N'TestLogFull'、
FILENAME = N'D:\\ DBFile \ \ TestLogFull \\ TestLogFull.mdf'、
SIZE = 500MB、
MAXSIZE = UNLIMITED、
FILEGROWTH = 100MB

ログオン

NAME = N'TestLogFull_log'、
FILENAME = N'D:\\ DBFile \\ TestLogFull \\ TestLogFull_Log.ldf'、
SIZE = 1MB、
MAXSIZE = 512MB
)。

ここで指定されているログファイルの最大サイズは512Mです。これは主に、ログスペースがいっぱいであるという現象を示すためです。
次に、新しいテーブルを開き、CDCを開いてテストします


USE TestLogFull
--CDCを有効にする
EXECUTEsys.sp_cdc_enable_db;
GO
-テストテーブルを作成する
テーブルtest_cdcを作成する

id int Identity(1,1)主キー、
名前nvarchar(50)、
メールvarchar(50)、
アドレスnvarchar(50)、
lastupdatetime datetime

--テーブルでCDCを有効にします
EXEC sys.sp_cdc_enable_table
@source_schema ='dbo'、
@source_name ='test_cdc'、
@role_name ='cdc_admin'、
@capture_instance = DEFAULT、
@supports_net_changes= 1、
@index_name = NULL、
@filegroup_name = DEFAULT

CDCが正常に開かれ、テストログがいっぱいになります。

これは、一部のテーブルでCDCがオンになっているときにログファイルがいっぱいになる状況のデモンストレーションです。
1、プロキシサーバーが起動しておらず、ログスペースがいっぱいです
本文の冒頭で述べたステップ3、CDCの場合、プロセスはSQLcdcです。***_ログを読み取るためのサーバーエージェントまたはレプリケーションエージェントジョブのキャプチャジョブ

CDC後にSQLServerエージェントがシャットダウンされた場合または、レプリケーションがオンになっているか、サーバーの再起動後にSQLServerエージェントがランダムに自動的に開始されない

これにより、手順2でログのバックログが発生する可能性があります。つまり、データの変更を記録した後のトランザクションログがレプリケーションの状態であり、再利用できないため、使用できるログはありません。データベースの操作時に、データベース'***'のトランザクションログのプロンプトが表示されます。 'REPLICATION'のためにいっぱいです。
プロキシサービスeはここで一時的にオフになっています(この現象をテストして実証するためだけに)

追加、削除、および変更により、トランザクションログが生成される可能性があります。これは、データを挿入し、SQLを作成してデータを書き込み、CDCを有効にしてデータベースをテーブルに書き込む場合です。

データベースが構築されたとき、CDCがあったため、ログファイルは512Mに制限されていました。このテーブルで有効になっていると、データを書き込むプロセスでトランザクションログが生成されます。ログの空き領域は、データの書き込みプロセスに限定されていました。最初は問題ありませんでした。データの継続的な書き込み(レプリケーション状態のログは常にバックログされます)で、ログがすべて使用されると、次のエラーが生成されます

このとき、トランザクションログの使用状況を観察し、完全に使用されていることを確認してください。

ログスペースが完全に使用されているため、ログの待機状態を確認します。これがレプリケーション状態です。

現時点では、ログがアクティブ状態であり、アクティブ状態のログを縮小できないため、縮小も無効です。

プロキシが閉じているため、ログを読み取るジョブを実行できず、ログがブロックされていることがわかります。次に、プロキシを開いて、それが機能するかどうかを確認しますか?

エージェントの電源を入れてCDCジョブの実行を確認すると、現時点ではエージェントジョブが使いにくく、ジョブの実行が成功せず、同じプロンプトが表示されます。トランザクションログがいっぱいであると言います

現時点では、監視テストテーブルのcdcターゲットテーブルにデータがないため、この時点でエージェントがオンになっていても、cdcジョブは正常に実行されません。

では、なぜですかCDCのプロキシジョブは正常に実行できませんか?


実際、理解するのは難しいことではありません。 cdcジョブは、トランザクションログからのデータの読み取りと書き込みも行います。これもトランザクションオペラに相当しますこれはログを使用して実装する必要があり、現時点では使用可能なログスペースがありません。


もちろん、この仕事は失敗するでしょう。

では、それではどうしますか?
ログがブロックされているため、アクティブログのこの部分をクリーンアップし、トランザクションログを分散としてマークしてみてください(これはCDCですが、ログの使用はレプリケーションと同じである必要があります) / p>

私のテストによると、上記のステートメントを実行し、複製されたものを分散としてマークした後、ログの使用状況を再度確認し、それがまだ100%であることを確認しますが、データの書き込みは成功し、その後データが再度書き込まれます、ログスペースが解放され始めます。書き込み時に分散としてマークされたログの切り捨てをトリガーする必要があります。つまり、100%上のログスペースを解放してから、ログの使用状況を監視します。予想どおり、ログのこの部分が切り捨てられ、ログスペースが完全ではなくなっていることがわかります。完全に占有され、ログはNothing(再利用可能)の状態になります

このテストは、CDCが有効になっている場合、SQL Serverエージェントが正常に起動しないか、対応するジョブが正常に起動しない場合、ログスペースが継続的な生成でいっぱいになり、データベースが書き込み操作を実行できなくなることを示しています。
ここでは、ログを手動で配布済みとしてマークすることにより、ログが解放されます。この場合、cdcログは壊れます。つまり、手動で解放されたログをダウンストリーム(cdcログテーブル)に配信できません。 )


結局のところ、それはあまり良い方法ではありません。別の方法を以下で説明します。
2、短期間の大規模なトランザクション操作により、ログスペースがいっぱいになります
上記の状況とは異なり、プロキシサービスが閉じられ、ログがブロックされます。ここでは、プロキシサービスが直接オンになっており、次のスクリプトを使用してテーブルにデータを書き込みます(たとえば、実際のビジネスでデータをバッチインポートします。クラス)


一定期間書き込んだ後も、トランザクションログはいっぱいになります。なんで?

CDCのエージェントタスクから始めましょう。このエージェントのJOBは継続的に実行されますが、上記のデータが書き込まれる場合、つまりログが継続的に生成される場合にも継続的に書き込まれます。


ログファイルのサイズには制限があるため(ここでは、デモンストレーションの便宜上、制限は512Mです)、ログファイルには最大スペース制限があります。


ここでは、セッションがログスペースを消費し(挿入操作)、プロセスがログを解析してログスペースを解放する(エージェントジョブ)と見なすことができます。


ただし、消費率は放出率よりも高くなります。ログスペースが使い果たされると、CDCプロキシジョブを完了できなくなります。


これにより、上記の状況が発生します。ログスペースがいっぱいになり、データベースが書き込みオペラを実行できなくなります。また、CDCジョブを実行して、再利用可能なログ領域を解放することはできません。
上記は、トランザクションログのステータスを手動でマークすることにより、入力されているログファイルを解決するためのものです。


ログを手動で配布済みとしてマークすることは、やや不適切です。


ログステータスが配布済みとしてマークされると、CDCシステムテーブルまたはサブスクライバーに渡されません


この問題を解決する別の方法は次のとおりです。現在のログがいっぱいであるため、ログを追加しています。新しく追加されたログの初期スペースは小さすぎないように注意してください。


(テストに関心のある方は、ここにログファイルを追加した後、1〜2分お待ちください)その後、後続のCDCジョブは、新しく追加されたログスペースを使用して実行を継続します。

この場合、ログのサイズが制限されている場合(またはログを保存するためのディスク容量が不足している場合)、データベースでCDCまたはレプリケーションが有効になります。e、


データが大量のバッチ(追加、削除、および変更)で継続的に書き込まれると、SQL Serverエージェントはログを可能な限り高速に解析および解放できなくなり、ログがいっぱいになる可能性もあります。
3、ログファイルを追加しないスペースまたはログファイルを追加する場合は、SQLServerサービスを再起動します
この現象を再現して解決しようとしたときにも、この方法を自分で試しました。実現可能性はそれほど強くはありませんが、Dafaを再起動することであることを説明したいと思います。同時に、再起動後にログファイルにいくつかの興味深い変更が加えられました。


データベースが構築されると、ログファイルは最大512Mに制限され、ログは手動で分散としてマークされませんが、SQL Serverサービスを再起動した後、ログが保存されるディスクにスペースがある場合、ログは自動的に展開されます。


次に、展開されたログのこの部分で、プロキシジョブは、(その後)レプリケーション状態でログを解析し、ログスペースを解放できます(レプリケートされるログの量によっては、ログの解析と解放に時間がかかります)< br />

下の図からわかるように、ログ制限は512MBですが、556MBに初期化されており、これは明らかに最大ログサイズよりも大きくなっています。これは、SQLServerサービスを再起動した結果によるものです。
以下は、SQL Server2014SP2バージョンでテストされた現象です。

SQL Server 2014(SP2以外のパッチバージョン)の場合、CDCを有効にしてログがいっぱいになった場合、つまり再起動後にログが自動的に展開されない場合、次のような状況は発生せず、私も酔っています。何かを確認するのは簡単ではありません。これらの小さな詳細はパッチバージョンにも関連していますが、この部分的な方法は経験として使用できません。
概要: strong> span>
CDCが有効な場合edの場合、関連するテーブルの変更はトランザクションログに書き込まれ(ログステータスはレプリケーションステータス)、プロキシタスクはログを解析し、解析日後にログを再構築可能としてマークします(単純な復元モードの場合) )、再利用可能です。完全復元モードの場合、ログバックアップはレプリケーション状態のログを切り捨てることができません)。この状態で、ログの最大サイズが制限されている場合、または制限がない場合、ログを保存するためのディスク容量が不足しており、大量のデータを書き込む(追加、削除、変更)場合、次の可能性があります。生成されたログはログファイルをいっぱいにします。これにより、プロキシジョブがログを解放して失敗します。プロキシジョブを実行できず、ログを解放できません。
この時点で、新しいログファイルを追加するか、ログファイルの最大サイズを増やすか、システムストアドプロシージャsp_repldone(transactionReusableトランザクションログをマーク)を実行してトランザクションを分散としてマークして解決します。この問題。
上記は、Xiaobianによって導入されたSQL ServerデータベースでCDCを開くことによって発生する「トランザクションログスペースがいっぱいです」の分析と解決策(REPLICATION)です。お役に立てば幸いです。ご不明な点がございましたら、メッセージを残してお送りください。編集者が時間内に返信いたします。ウェブサイトへのご支援ありがとうございました!