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

SQLServer database open CDC caused by transaction log space is full of what is the reason, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

When CDC is enabled in SQLServer, the phenomenon that in some cases will cause the transaction log space to be full is:

The transaction log for database'* *'is full due to 'REPLICATION' (database "*" transaction log is full because of "REPLICATION") is prompted during the execution of add, delete and change statements (generate transaction log).

Roughly speaking, the basic principles of CDC and replication, the steps for using logs are as follows:

1. Whenever a transactional operation (addition, deletion or modification) occurs in the underlying table (a table with CDC or replication turned on), the corresponding transaction log is written to the log file

2. At this time, the log is marked as Replication, that is, it is in the state to be replicated. This active state has nothing to do with the restore mode of the database, even if it is a simple restore mode.

3, and then the background process reads the log and writes to the target table according to the memory of the transaction log

This goal for cdc is a system table that records changes in data.

For replication, it is written to the library distribution.

4. After the completion of step 3, the transaction log is marked as normal. If it is a 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, release the available log space and write to the database again, it will appear: the transaction log of the database "TestDB" is full. Cases where the reason is "REPLICATION"

In this paper, by demonstrating the phenomenon that log space is occupied when CDC is turned on, and the corresponding processing methods.

Test environment building

First set up a test database

USE masterGOCREATE DATABASE TestLogFull ON PRIMARY (NAME = DBFile\ TestLogFull\ TestLogFull.mdf', SIZE = 500MB, MAXSIZE = UNLIMITED, FILEGROWTH = 100MB) LOG ON (NAME = disabled:\ DBFile\ TestLogFull\ TestLogFull_Log.ldf', SIZE = 1MB, MAXSIZE = 512MB)

The maximum log file specified here is 512m, mainly to demonstrate the phenomenon that the log space is full.

Then open a new table and open CDC to test

USE TestLogFull-- enables CDCEXECUTE sys.sp_cdc_enable_db GO-- creates a test table create table test_cdc (id int identity (1) primary key, name nvarchar (50), mail varchar (50), address nvarchar (50), lastupdatetime datetime)-- enable CDCEXEC sys.sp_cdc_enable_table @ source_schema = 'dbo', @ source_name =' test_cdc', @ role_name = 'cdc_admin', @ capture_instance = DEFAULT, @ supports_net_changes = 1 for the table @ index_name = NULL, @ filegroup_name = DEFAULT

CDC is enabled successfully. Start testing if the log is full.

This demonstrates how log files are full when CDC is turned on for some tables.

1. The log space is full due to the failure to start the proxy server

Step 3 mentioned at the beginning of the article, for CDC, the process is the cdc.***_capture job or replication agent job in SQL Server Agent to read logs. If SQL Server Agent is closed after CDC is turned on or after replication, or if SQL Server Agent does not start automatically at random after restarting the server, it is possible to cause the log backlog in step 2, that is, the transaction log after recording data changes is in replication state and cannot be reused. Causes that there are no logs available and prompts The transaction log for database'* *'is full due to 'REPLICATION'. when operating the database

The proxy service is temporarily closed here (just to test and demonstrate this phenomenon)

Both additions, deletions and modifications can generate transaction logs. Here we demonstrate the situation of insert data. Do a SQL to write data to the table that opens CDC. When the database is built, the log file is limited to 512m. Because CDC is turned on on this table, the process of writing data will generate transaction logs, and the log space is limited to the process of writing data, so there is no problem at first. As the data is constantly written (the log of Replication status continues to backlog), after the log is fully used, the following error report will be generated

At this point, observing the use of the transaction log, it is found that it has been fully used.

Because the log space is fully used, take a look at the wait state of the log, which is the Replication state

It is also invalid to try to shrink at this time, because the logs are all active, and the active logs cannot be shrunk.

It can be seen that because the agent is closed, the job of reading the log cannot be executed, causing the log to be blocked, so turn on the agent to see if it is all right. When you open the agent and check the execution of the CDC job, you will find that the agent job does not work well at this time, and the execution of the job is not successful, which indicates that the transaction log is full.

At this time, the cdc target table of the test table does not have any data, indicating that even if the agent is enabled, the cdc job is still not executed successfully, so why can the CDC agent job not be executed properly?

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

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 and try to mark the transaction log as distributed (although this is CDC, the use of logs should be the same as replication)

According to my test, after executing the above statement and marking the copied things as distributed, I checked the log usage again and found that it was still 100%, but the attempt to write the data was successful. After writing the data again (just one entry), the log space begins to be freed, and the log truncation marked as distributed should be triggered at the time of writing. That is, to release 100% of the log space above and then observe the log utilization, 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 (reusable).

This test shows that if the CDC,SQL Server agent does not start normally or the corresponding job does not start properly, the log space will be filled up with the continuous generation of things, resulting in the database unable to perform write operations.

Here, the log is released by manually marking the log as distributed, in which case the cdc log is broken, that is, the manually released log cannot be delivered downstream (cdc log table)

After all, it is not a very good idea, and another way will be explained below.

2, the log space is full caused by a large transactional operation in a short period of time

In the case of log congestion caused by the shutdown of the agent service mentioned above, the agent service is directly opened here, and the following script is still used to write data to the table (such as importing data in bulk in actual business, etc.)

After writing for a period of time, the transaction log is still full. Why?

Let's also start with the agent task of CDC. Although the JOB of this agent is executed continuously, it is also written continuously when the data is written above, that is, the log is generated continuously.

Because the size of the log file is limited (here it is limited to 512m for demonstration purposes), the log file has a maximum usage space limit.

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

However, the consumption speed is higher than the release speed, and once the log space is used up, the CDC agent job cannot be completed.

This leads to the above situation: the log space is filled, the database cannot perform any write operations, and CDC jobs cannot be performed, freeing up reusable log space.

The log file is filled up by manually marking the status of the transaction log

It is a bit inappropriate to mark the log as distributed manually.

Once the log status is marked as distributed, it will not be passed to the CDC system table or subscriber

Here is another way to solve this problem: since the current log is full, add a log and make sure that the initialization space for the new log is not too small.

(for those who are interested in testing, please wait patiently for a minute or two after adding log files here.) then the subsequent CDC jobs will continue with the help of the newly added log space.

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

Once a large number of persistent write operations (additions, deletions and corrections) occur, the speed of the SQL Server agent parsing and releasing the log will not keep up, and it may also cause the log to be full.

3. Restart the SQLServer service without increasing the log file space or adding log files

This method is also tried by me when I reproduce this phenomenon and try to solve it. The feasibility is not very strong, but let me make it clear that it is to restart the Dafa. At the same time, some interesting changes have taken place in the log files after the restart.

When building the database, the log file is limited to a maximum of 512m, and the log is not manually marked as distributed, but after restarting the SQLServer service, if the disk where the log is stored has space, the log will be automatically expanded.

Then, with this extended log, the agent job can parse the log of Replication status (after that) and free up the log space (it will take some time to parse and release the log, depending on the number of logs to be replicated)

The log is limited to 512MB, but initialized to 556MB, which is significantly larger than the maximum log size, thanks to the result of restarting the SQLServer service

The phenomenon of testing under SQL Server 2014 SP2 version

If it is SQL Server 2014 (non-SP2 patch version), the way to turn on CDC to fill the log will not occur as follows, that is to say, restarting a log will not automatically expand part of it. I am also drunk. It is not easy to verify something. These small details also have something to do with the patch version, but this partial approach cannot be used as experience!

When CDC is enabled, the changes on the relevant tables will be written to the transaction log (the log state is in Replication state), and the agent task will parse the log and mark the log as reconstructable after the parsing day (if it is a simple restore mode, it is reusable, if it is a full restore mode, the log backup cannot truncate the log in the Replication state). In this state, if the maximum size of the log is limited to small. Or there is no limit, the disk space for storing logs is insufficient, and when writing data in large quantities (adding, deleting and modifying), it is possible that the logs will fill up the log files, which will result in the failure of the agent job to release the log. The agent job cannot be carried out and cannot release the log, as if it is an endless cycle.

At this point, either add the log file or increase the maximum size of the log file, or you can solve this problem by executing the system stored procedure sp_repldone to mark the transaction as distributed (marking the transaction log reusable).

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.