Shulou(Shulou.com)05/31 Report--

This article to share with you is about SQL Server database to open CDC due to REPLICATION caused by the transaction log space is full of how to do, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some harvest, not much to say, follow Xiaobian to see it.

After CDC is enabled in SQL Server, the phenomena that will cause the transaction log space to be full in some cases are:

The transaction log for database '***' is full due to 'REPLICATION'.

CDC and Replication Fundamentals Roughly speaking, the steps for using logs are as follows:

1. Every time a basic table (a table with CDC or replication enabled) generates a transactional operation (add, delete and modify), the corresponding transaction log is written to the log file.

2. At this time, the log is marked as Replication, that is, in the state of waiting for replication. This active state has nothing to do with the restore mode of the database. Even if it is simple restore mode,

3, then there is a background process to read this log, write to the target table according to the memory of the transaction log,

This target for cdc is a system table that records data changes,

For replication, write to distribution.

4. After step 3, the transaction log is marked as normal. If it is simple restore mode, the transaction log parsed by the background process is truncated and can be reused.

If there is a problem in the third step above, that is, the background process cannot parse the log and release the available log space, and writes to the database again, it will appear that the transaction log of the database "TestDB" is full, and the reason is "REPLICATION".

Xiao Bian demonstrates the phenomenon that the log space is full when CDC is turned on, and the corresponding processing method.

Test environment setup

First, a test database is built,

USE master GO CREATE DATABASE TestLogFull ON PRIMARY ( NAME = N'TestLogFull', FILENAME = N'D:\DBFile\TestLogFull\TestLogFull.mdf' , SIZE = 500MB , MAXSIZE = UNLIMITED, FILEGROWTH = 100MB ) LOG ON ( NAME = N'TestLogFull_log', FILENAME = N'D:\DBFile\TestLogFull\TestLogFull_Log.ldf' , SIZE = 1MB , MAXSIZE = 512MB )

The *** of the log file specified here is 512M, mainly to demonstrate that the log space is full.

Then open a new table and open CDC to test

USE TestLogFull --Enable CDC EXECUTE sys.sp_cdc_enable_db; GO --Create a test table test_cdc ( id int identity(1,1) primary key, name nvarchar(50), mail varchar(50), address nvarchar(50), lastupdatetime datetime ) --Enable CDC EXEC sys.sp_cdc_enable_table on the 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 is enabled successfully, and the test log is full.

Here is a demonstration of log file fullness with CDC enabled for certain tables

1, proxy server not started causes log space to be full

Step 3 mentioned at the beginning of this article, for CDC, the process is cdc.***_in SQL Server Agent capture job or copy agent job to read logs

If SQL Server Agent is shut down after CDC or replication is turned on, or SQL Server Agent does not start automatically after restarting the server

It is possible that the logs in step 2 are overstocked, that is, the transaction logs after recording data changes are in replication state and cannot be reused, resulting in no usable logs.

The transaction log for database '***' is full due to 'REPLICATION'.

The proxy service is temporarily disabled here (just to test the phenomenon)

Add, delete and change can generate transaction log, here is the demonstration insert data situation, do a write data SQL, to open the CDC table to write database

When the database was being built, the log file was limited to 512M, because CDC was enabled on this table. The process of writing data would generate transaction logs, and the log had free space limit.

In the process of writing data, there is no problem at first. As the data is continuously written (the Replication status log is constantly overstocked), when the log is all used, the following error will occur.

At this point observe the transaction log usage, found that it is fully used,

Since the log space is fully used, observe the wait state of the log, which is the Replication state

Trying to shrink at this point is also ineffective because logs are active and active logs cannot be shrunk

It can be seen that because the agent is turned off, the job of reading the log cannot be executed, causing the log to be blocked, so turn on the agent to see if it works.

Open the agent and check the execution status of CDC job. You will find that the agent job is not working at this time. When the job is executed, it is not successful. The same prompt says that the transaction log is full.

At this time, observe that there is no data in the cdc target table of the test table, which means that even if the agent is turned on at this time, the cdc job is still not successfully executed.

So why is CDC's proxy job not working properly here?

In fact, it is not difficult to understand that cdc's job is also to read transaction logs and write data. This is equivalent to transactional operations, which must be implemented with the help of logs. At this time, there is no available log space.

Of course, this assignment will fail.

So what do we do now?

Since the log is blocked, find a way to clean up this part of the active log, try to mark the transaction log as distributed (although this is CDC, but the use of the log should be the same as replication)

According to my tests, after executing the above statement to mark copied things as distributed,

Check the log usage again and find that it is still 100%, but when trying to write data, it is successful. After writing data again (one entry is enough), the log space starts to be released.

Log truncation marked as distributed should be triggered at write time, that is, the log space occupied by 100% is released

Then observe the usage of the log, and find that as expected, this part of the log has been truncated, the log space is no longer fully occupied, and the log becomes Nothing state (reusable).

This test shows that if CDC is enabled, SQL Server Agent does not start normally or the corresponding job does not start normally, the log space will be filled with continuously generated things, resulting in the database unable to perform writeable operations.

Here, logs are released by manually marking logs as distributed. In this case, cdc logs will be broken, that is, manually released logs cannot be delivered to downstream (cdc log table).

After all, it is not a very good way, and the following will explain another way.

2. Log space occupied by large transactional operations in a short period of time

For the case where the proxy service is turned off and the log is blocked, the proxy service is directly turned on here, and the following script is still used to write data to the table (such as batch import data in actual business).

After writing for a while, the transaction log is still full. Why is this?

We should also start from CDC's agent task. Although the JOB of this agent is continuously executed, because the data written above is also continuously written, that is, the log is continuously generated.

Because the size of the log file is limited (here, for the convenience of demonstration, the limit is 512M), the log file has *** space restrictions.

This can be thought of as a Session consuming log space (Insert operation), a process parsing log after releasing log space (agent job),

But it consumes faster than it releases, and once the log space is used up, CDC's agent jobs cannot be completed,

This again creates the above situation: log space is full, the database cannot perform any write operations, CDC jobs cannot be performed, freeing up reusable log space,

The above is to resolve log file fullness by manually marking the status of the transaction log,

Manual marking of logs as distributed is somewhat inappropriate,

Once a log is marked as distributed, it is not passed to CDC's system tables or subscribers

Here is another way to solve this problem: since the current log is full, add a log, pay attention to the space for initialization of the new log is not too small.

(If you are interested in testing, please wait patiently for one or two minutes after adding the log file) Then the subsequent CDC job will continue to execute with the help of the newly added log space.

This situation indicates that if the log size is limited (or there is not enough disk space to store logs), CDC or replication is enabled in the database,

Once a large number of persistent data write operations (add, delete and modify) occur, SQL Server Agent will not be able to keep up with the speed of parsing and releasing logs, or it may cause logs to be full.

3. Restart SQL Server service without increasing log file space or adding log files

This method is also when I try to reproduce this phenomenon and try to solve it, the feasibility is not too strong, but still explain, that is, restart Dafa, and some interesting changes have taken place in the log file after the restart.

Log files are limited to *** 512M when the database is created, and there is no manual marking of the log as distributed. However, after restarting SQL Server service, if there is space on the disk where the log is stored, the log will automatically expand by a portion.

Then with this expanded log, the agent job can parse the Replication status log (and then) free up log space (it takes a while to parse and release the log, depending on the amount of logs to be replicated)

As you can clearly see in the figure below, the log limit is 512MB, but the initialization is 556MB, which is significantly larger than the *** log size. This is due to the restart of SQL Server service.

Here are the phenomena tested under SQL Server 2014 SP2 version.

If it is SQL Server 2014(not SP2 patch version), the way to open CDC occupies the full log, and the following situation will not occur, that is, restarting the log will not automatically expand a part.

When CDC is turned on, changes in correlation tables are written to the transaction log (log status is Replication status),

The agent task parses the logs and marks the logs as reconstructable (reusable if simple restore mode is used, and log backup cannot truncate the logs in Replication state if full restore mode is used).

In this state, if the log size is limited, or if there is no limit, the disk space for storing the log is insufficient,

When writing data in large quantities (adding, deleting and modifying), it is possible to generate a log that occupies the full log file.

Agent jobs that release logs cannot be performed, agent jobs cannot be performed, and logs cannot be released, as if in an endless loop.

Either add a new log file or increase the size of the log file, or resolve the problem by executing the system stored procedure sp_repldone to mark the transaction as distributed (mark the transaction log for reuse).

The above is how to open CDC in SQL Server database due to REPLICATION, which causes transaction log space to be full. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.