Database log growth & CDC

• VastSQL

  SSC-Dedicated

  Points: 36200

  More actions

  March 2, 2023 at 5:50 am

  #4155798

  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

• Emperor100

  SSCrazy

  Points: 2205

  More actions

  March 3, 2023 at 3:11 am

  #4156273

  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.

  =======================================================================

• VastSQL

  SSC-Dedicated

  Points: 36200

  More actions

  March 13, 2023 at 6:13 am

  #4160002

  Thanks Emperor100 but my question is about the open transaction .

  The query is from Informatica tool which is causing this issue. Anyone have any idea why this is happening.

  • This reply was modified 2 years, 1 month ago by  VastSQL.