SQL DBA Diaries of Pradeep Adiga. I got a chance work on CDC job failure issue recently. Ms Access 2007 Free Download Full Version For Windows 7. The customer had configured CDC on the database but it wasnt working as expected. Hence he had disabledenabled CDC multiple times on this database. The cdc. DBNamecapture job was failing with the following error. Message 2. 28. 58, Level 1. State 1. Unable to add entries to the Change Data Capture LSN time mapping table to reflect dml changes applied to the tracked tables. Refer to previous errors in the current session to identify the cause and correct any associated problems. SQLSTATE 4. 20. 00 Error 2. The statement has been terminated. SQLSTATE 0. 10. 00 Error 3. NOTE The step was retried the requested number of times 1. The step failed. Since the error message had reference to Change Data Capture LSN time mapping table, I looked into the cdc. On a normal CDC configuration this table would have an entry for each transaction that was captured. But in this case, there was only one entry but the tranbegintime and tranendtime columns had a value which was a couple of days old. Also the tranbeginlsn was printed as 0x. Ssms Tool Pack Alternative' title='Ssms Tool Pack Alternative' />Slow in the Application, Fast in SSMS Understanding Performance Mysteries. An SQL text by Erland Sommarskog, SQL Server MVP. Last revision 20170920. Hinweis. Dieses Thema bietet einen berblick ber die Firewallkonfiguration und fasst Informationen zusammen, die fr einen SQL Server SQL ServerAdministrator. You can do this with SSMS. Rightclick your database in Object Explorer. Select TasksGenerate Scripts. On the Set Scripting Options page, click the. Career advice for the IT professional, as well as tips for SQL Server and Business Intelligence. This isnt normal. To get more details about this error, I added a verbose log to the CDC capture job. The verbose log printed the following message when the job was running. Violation of PRIMARY KEY constraint lsntimemappingclusteredidx. Cannot insert duplicate key in object cdc. The duplicate key value is 0x. CF8 0. 00. 55 Unable to add entries to the Change Data Capture LSN time mapping table to reflect dml changes applied to the tracked tables. From the verbose log it was evident that the CDC capture job was trying to insert a duplicate row the object cdc. The sys. databases DMV reported that the logreusewaitdesc was REPLICATION. NOTHINGtempdb 2 NOTHINGmodel 3 LOGBACKUPmsdb 4 NOTHING CDCDBNAME 5 REPLICATIONThis indicated that another CDC or replication was active on this database. Since there was no replication configured on this instance, it had to be a CDC job. DBCC OPENTRAN reported that there was another CDC transaction was active on this database. Transaction information for database cdcdbname. Replicated Transaction Information Oldest distributed LSN 0 0 0Oldest non distributed LSN 5. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Since customer had attempted enablingdisabling CDC multiple there was a possibility that a stale CDC transaction was still active on the database. To clear the article cache of CDC, executed spreplflush against the database in question. After restarting the job, it again failed but with a different error message now. Another connection is already running spreplcmds for Change Data Capture in the current database. This list contains all of the known Microsoft Knowledge Base articles, howtos, fixes, hotfixes, webcasts and updates of Microsoft SQL Server 2005 starts with letter F. PostgreSQL vs. MS SQL Server. A comparison of two relational databases from the point of view of a data analyst. I had encountered an issue while installing a SQL Server service pack. The resolution for it was to replace the existing SQL Server binaries from that of a Service Pack. DBCC OPENTRAN reported that there was a open transaction on the database but it was a user transaction which wasnt running spreplcmds. Executed spreplflush one more time and restarted the job again. This time it didnt fail and also we started seeing rows getting inserted into all the CDC related tables. Since the issue at hand was resolved, didnt delve into the root cause. Most likely it was a stale CDC transaction which wasnt cleaned up when CDC was disabled on the database.