locked
CDC error after RESTORE FOR ATTACH_REBUILD_LOG RRS feed

  • Question

  • I was forced to restore a DB using the FOR ATTACH_REBUILD_LOG option.

    Problem was that the DB was configured to use CDC which is not working properly anymore because the capture SQL Job gives following error:

    The Change Data Capture 'capture' job does not exist in the system table 'msdb.dbo.cdc_jobs'. Use the stored procedure 'sys.sp_cdc_add_job' to add the Change Data Capture job. [SQLSTATE 42000] (Error 22997)

    All capture tables are available.

    Is there a way to recover the CDC process without loosing the CDC table configurations?

    Thanks

     

    Tuesday, January 10, 2012 4:24 PM

Answers

  • Hi geertDCOD, 

    In your scenario, the Capture and Cleanup jobs are required to be manually created after restore if there are not existed in this database:
    use <databasename>
    GO
    exec sys.sp_cdc_add_job 'change'
    GO
    exec sys.sp_cdc_add_job 'cleanup'
    GO
    
    

    I have tried to create a database with "CREATE DATABASE...FOR ATTACH_REBUILD_LOG" command to restore a database only with the MDF data file  different database name, which is CDC enabled on the same instance. After that, create these two jobs and the CDC works on the new database.

    TechNet Subscriber Support

    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Stephanie Lv

    TechNet Community Support



    Wednesday, January 11, 2012 2:58 AM

All replies

  • Did you restore MSDB database?
    http://uk.linkedin.com/in/ramjaddu
    Tuesday, January 10, 2012 9:29 PM
  • Hi geertDCOD, 

    In your scenario, the Capture and Cleanup jobs are required to be manually created after restore if there are not existed in this database:
    use <databasename>
    GO
    exec sys.sp_cdc_add_job 'change'
    GO
    exec sys.sp_cdc_add_job 'cleanup'
    GO
    
    

    I have tried to create a database with "CREATE DATABASE...FOR ATTACH_REBUILD_LOG" command to restore a database only with the MDF data file  different database name, which is CDC enabled on the same instance. After that, create these two jobs and the CDC works on the new database.

    TechNet Subscriber Support

    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Stephanie Lv

    TechNet Community Support



    Wednesday, January 11, 2012 2:58 AM
  • Hi,

    Believe it or not, we have the same issue that forced me to perform a "CREATE DATABASE...FOR ATTACH_REBUILD_LOG" command.

    -       Tranlog on a CDC-enabled DB is full 100%

    -      Results of dbcc loginfo: Status=2 for all VLF's

    -      Result of dbcc opentran():

    Transaction information for database 'Archive_WISH'.

     

    Replicated Transaction Information:

            Oldest distributed LSN     : (0:0:0)

            Oldest non-distributed LSN : (40330:25169:1)

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Last time we experienced this behavior nothing of the following helped

    -      Put DB in simple recovery mode and back into FULL

    -      Execute tranlog backup and shrink multiple times

    -      Stop SQL Agent to make sure no more transactions would be started

    -      Restart SQL Server service

    Can somebody advice what can be done?

     


    Geert Vanhove
    Friday, January 13, 2012 11:14 AM
  • geertDCOD,

    So it is another problem about the transaction log file growing drastically since the log records stay active, which cannot be reuse. Like the transactional replication and database mirroring, CDC uses the transactional replication log reader agent to collect data changes from the transaction log. The log records will stay active until they are captured by the change data capture scan job.

    In this case, you may pay attention to the capture job to see if it works successfully, and check the Error Logs about SQL Server Agent. Also, please query the sys.dm_cdc_errors table to see whether there are errors during the change data capture log scan sessions. In addition, view the sys.dm_cdc_log_scan_sessions table to see the last record about the latest log scan session in the current database.

    For more information: Tuning the Performance of Change Data Capture in SQL Server 2008.

    I would recommend you open a new thread and provide the relevant information which I mentioned above if the issue cannot be eliminated.

     


    Stephanie Lv

    TechNet Community Support

    Saturday, January 14, 2012 6:14 AM
  • thanks,

    I added this issue because this was the reason why I was forced to run the initial restore FOR ATTACH_REBUILD_LOG.

    A new thread can be found here:http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/ac4a5263-0da0-49fb-87ee-7e5e3c8c32f9

     


    Geert Vanhove
    Monday, January 16, 2012 9:17 AM