March 2, 2023 at 5:50 am
HI Experts,
I have enabled CDC on about 20 databases as part of BI in one of our production server. Later I faced with the issue in which the log file of one of our database grew and filled the entire disk. I found this command as open transaction .
BEGIN TRAN;INSERT INTO dbo.[$InfaXact] VALUES('SSLR Capture (Agent ProcessId:13416 Started:2023-02-28 17:11:52.000) User:' + suser_name() + ' Transaction Time:' + CONVERT(VARCHAR(50), GETDATE(), 120));
Now the same process is running all the time , what exactly is this and how can I make sure this wont cause any trouble.
Regards
TIA
March 3, 2023 at 3:11 am
When you enable CDC (Change Data Capture) on a database. It creates two SQL Agent Jobs in the same instance. Every DML operation is captured for each table whether it is (DELETE, INSERT, BEFORE UDPATE, AFTER UPDATE), it will also creates a separate cdc schema. (you can see it under system tables in SSMS).
Your log files will grow faster if you don't move the data to a different database... to maintain history. This kind of setup is used to audit and also build any data marts for Enterprise data warehouse systems. There is a retention setting as well that you can use to set the # of days to keep the data under CDC schema.
In my opinion, 20 databases is too much unless you have a robust tool to migrate the data to a different databases respectively for other business needs.
=======================================================================
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply