locked
CDC is not working in SQL server 2014 SP2 CU10 RRS feed

  • Question

  • Hi,

    I have CDC enabled in one of the SQL Server database  2014 sp2 CU10 in AG group.CDC job has been stopped since few months back.

    I can't see any error but messages in CDC view history and found no error except messages:

    ------------------------------------------------------------------------

    Date 30/06/2018 11:09:10 AM
    Log Job History (cdc.comp2_capture)

    Step ID 2
    Server SQL1
    Job Name cdc.comp2_capture
    Step Name Change Data Capture Collection Agent
    Duration 2.19:04:23
    Sql Severity 10
    Sql Message ID 22803
    Operator Emailed
    Operator Net sent
    Operator Paged
    Retries Attempted 0

    Message
    Change Data Capture has scanned the log from LSN{000229B7:001D5518:0001} to LSN{000229B7:001D5568:001B}, 6 transactions with 23 commands have been extracted. To report on the progress of the operation, query the sys.dm_cdc_log_scan_sessions dynamic management view.

    I have checked cdc_help_jobs

    A2310A21-3401-4C56-B6AC-3A09FDD777A5 capture cdc.comp2_capture 500 10 1 5 0 0
    7225737D-FE8C-4646-BAB0-526C6BD15663 cleanup cdc.comp2_cleanup 0 0 0 0 2880 5000

    also checked sys.dm_cdc_log_scan_sessions  

    session_id start_time end_time duration error_count start_lsn current_lsn end_lsn last_commit_lsn last_commit_time log_record_count command_count first_begin_cdc_lsn last_commit_cdc_lsn last_commit_cdc_time latency empty_scan_count failed_sessions_count
    35857 2018-07-02 13:39:07.043 2018-07-02 13:41:52.130 0 Done 00000000:00000000:0000 00000000:00000000:0000 00000000:00000000:0000 0 1900-01-01 00:00:00.000 0 0 00000000:00000000:0000 00000000:00000000:0000 1900-01-01 00:00:00.000 0 34 0

    Please help me out.

    Regards,

    Manisha

    Monday, July 2, 2018 3:44 AM

Answers

  • Hi Manisha,

    Based on my research, it seems like this is a known issue. As:

    Microsoft SQL Server Product team has identified a potential issue with the latest Servicing Releases for SQL 2012, 2014 and 2016, where in Change Data Capture functionality might break if

    1. The databases enabled for CDC are part of Always On availability group, OR
    2. SQL Server replication components are not installed on the server

    And here's the workaround:

    1. As a recommended resolution for the first scenario, users can perform either of the following:
       a. After a secondary replica is upgraded, perform a failover to make it the primary, and run sp_vupgrade_replication.
       b. Disable automatic failover and perform upgrade at the primary replica. If automatic failover is needed, it can be re-enabled after upgrade. Please note that this approach will result in database unavailability during upgrade.

    2. To work around the second scenario, users can run "sp_cdc_vupgrade" or "sp_vupgrade_replication" against the database(s) enabled for CDC after the upgrade.

    For more details, please refer: https://blogs.msdn.microsoft.com/sql_server_team/cdc-functionality-may-break-after-upgrading-to-the-latest-cu-for-sql-server-2012-2014-and-2016/ 

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Xi Jin Tuesday, July 3, 2018 5:54 AM
    • Proposed as answer by Xi Jin Tuesday, July 10, 2018 7:20 AM
    • Marked as answer by Olaf HelperMVP Saturday, November 10, 2018 6:36 AM
    Tuesday, July 3, 2018 5:54 AM

All replies

  • Hi Manisha,

    Based on my research, it seems like this is a known issue. As:

    Microsoft SQL Server Product team has identified a potential issue with the latest Servicing Releases for SQL 2012, 2014 and 2016, where in Change Data Capture functionality might break if

    1. The databases enabled for CDC are part of Always On availability group, OR
    2. SQL Server replication components are not installed on the server

    And here's the workaround:

    1. As a recommended resolution for the first scenario, users can perform either of the following:
       a. After a secondary replica is upgraded, perform a failover to make it the primary, and run sp_vupgrade_replication.
       b. Disable automatic failover and perform upgrade at the primary replica. If automatic failover is needed, it can be re-enabled after upgrade. Please note that this approach will result in database unavailability during upgrade.

    2. To work around the second scenario, users can run "sp_cdc_vupgrade" or "sp_vupgrade_replication" against the database(s) enabled for CDC after the upgrade.

    For more details, please refer: https://blogs.msdn.microsoft.com/sql_server_team/cdc-functionality-may-break-after-upgrading-to-the-latest-cu-for-sql-server-2012-2014-and-2016/ 

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    • Edited by Xi Jin Tuesday, July 3, 2018 5:54 AM
    • Proposed as answer by Xi Jin Tuesday, July 10, 2018 7:20 AM
    • Marked as answer by Olaf HelperMVP Saturday, November 10, 2018 6:36 AM
    Tuesday, July 3, 2018 5:54 AM
  • Hi All,

    Can anyone help me on this CDC issue?

    I am not sure my CDC is working fine or not.Just want to make sure is working okay

    How should I verify that CDC is working properly?

    Attaching screenshots as well.

    Regards,

    Manisha K S


    • Edited by ken Myer Wednesday, August 29, 2018 6:51 AM
    Wednesday, August 29, 2018 6:50 AM