none
Disabling of CDC for a database fails

    Question

  • We have a database with cdc enabled for several tables, but when trying to disable cdc for the database this error happens:

    Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 216
    Could not update the metadata that indicates database AgricultureSubsidy is not enabled for Change Data Capture. The failure occurred when executing the command '.sys.sp_replhelp N'DisablePerDbHistoryCache''. The error returned was 3930: 'The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.'. Use the action and error to determine the cause of the failure and resubmit the request.
    Msg 266, Level 16, State 2, Procedure sp_cdc_disable_db_internal, Line 0
    Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 2.
    Msg 266, Level 16, State 2, Procedure sp_cdc_disable_db, Line 0
    Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 2.
    Msg 3998, Level 16, State 1, Line 1
    Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

    The error has been described on Microsoft connect and reported fixed back in 2008.

    The same error now appears with both without SP1 and with SP1, on sql server version:

    Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)   Mar 29 2009 10:11:52   Copyright (c) 1988-2008 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) (VM)

    We have disabled all of the tabled for cdc, but can not disable database.

    How can this be fixed?
    Thursday, November 5, 2009 8:13 PM

Answers

  • Ok this hole thing happend again, and re-enabling the cdc_cleanup_job did not work this time.

     

    So I dug around a bit and after I deleted all of the cdc objects and schema from my database SQL server still would not comply. So a couple of more hours of hair pulling until I came accross a table dbo.cdc_jobs in msdb database. Deleted all records from the database and the sys.sp_cdc_disable_db  stored procedure removed CDC as it should have in the first place.

     

    So it seams that deleting jobs from the msdb.dbo.cdc_jobs did the trick this time around.


    • Marked as answer by Luka Ferlež Tuesday, February 22, 2011 12:02 AM
    Tuesday, February 22, 2011 12:02 AM
  • We have managed to resolve the problem ourselves. The problem as we can figure it was in our deliberate removal of cdc_cleanup job.

     

    We have thought that the cdc_cleanup job just deletes all the entries from the cdc_xxxx tables that have been collected during the cdc_collect job. Then we created an SP to delete records from the cdc_xxxx tables and removed the job. The result of that was that the error as descried began to appear after a few days. We have accidentally turned on the job back on one of our databases and miraculously the error went away. We the tired to turn on the job back on the other databases and the error was no more.

     

    It seams that the cdc_cleanup job does a little more than just delete the collected records and that the deleting it was the cause of the problem in these instance.

    In short: Do not remove the cdc_cleanup job!

    • Marked as answer by Luka Ferlež Tuesday, January 19, 2010 2:48 PM
    Tuesday, January 19, 2010 2:47 PM

All replies

  • Can you give more detail about how this is being executed?  Via TSQL?  Inside an app?  Is it wrapped in a transaction?

    THe underlying eror is "The failure occurred when executing the command '.sys.sp_replhelp N'DisablePerDbHistoryCache''".  Are you able to execute this statement manually?

    This may end up being a bug and you'll have to open a support case to have an engineer investigate the problem in more detail.

    Friday, November 6, 2009 11:33 PM
    Moderator
  • The statment is being executed via TSQL from Manangement Studio, it is not wrapped in a transaction. We have tired to detach/attach the database with no change in behaviour.
    Wednesday, November 11, 2009 8:46 AM
  • We have managed to resolve the problem ourselves. The problem as we can figure it was in our deliberate removal of cdc_cleanup job.

     

    We have thought that the cdc_cleanup job just deletes all the entries from the cdc_xxxx tables that have been collected during the cdc_collect job. Then we created an SP to delete records from the cdc_xxxx tables and removed the job. The result of that was that the error as descried began to appear after a few days. We have accidentally turned on the job back on one of our databases and miraculously the error went away. We the tired to turn on the job back on the other databases and the error was no more.

     

    It seams that the cdc_cleanup job does a little more than just delete the collected records and that the deleting it was the cause of the problem in these instance.

    In short: Do not remove the cdc_cleanup job!

    • Marked as answer by Luka Ferlež Tuesday, January 19, 2010 2:48 PM
    Tuesday, January 19, 2010 2:47 PM
  • I seem to be experiencing a similar issue regarding CDC and VS2010's dbproj Project.  Does anyone have a workaround for Change Data Capture used in conjunction with a VS2010 Database Project?

     

    Disable CDC at database level...
    Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 216
    Could not update the metadata that indicates database dbname is not enabled for Change Data Capture. The failure occurred when executing the command '.sys.sp_replhelp N'DisablePerDbHistoryCache''. The error returned was 916: 'The server principal "S-1-9-3-152444072-1325000294-3761996221-442582315." is not able to access the database "dbname" under the current security context.'. Use the action and error to determine the cause of the failure and resubmit the request.
    Msg 266, Level 16, State 2, Procedure sp_cdc_disable_db_internal, Line 0
    Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
    Msg 266, Level 16, State 2, Procedure sp_cdc_disable_db, Line 0
    Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
    CDC has been disabled
    Msg 3998, Level 16, State 1, Line 1
    Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

     

     


    Tim Carper
    • Edited by Tim Carper Tuesday, May 25, 2010 3:12 PM
    Tuesday, May 25, 2010 1:53 PM
  • You should probably open a new thread, but here goes.

    What were you actually doing when this error occurred? From the error message I would say that the user used to execute the command does not have sufficient security privileges.

     

    The server principal "S-1-9-3-152444072-1325000294-3761996221-442582315." is not able to access the database "dbname" under the current security context.

    Tuesday, May 25, 2010 2:56 PM
  • You're probably right about the new thread idea.  If this doesn't go anywhere this morning I will do so.

     

    I'm in VS2010 & TFS2010 with a dbproj for a Database and another dbproj for the Server (I created this Server project today on the suggestion of another thread to try and get around this permissions issue).

    The server's local to me and I have full control over its permissions, however I'm struggling to get this automatic Visual Studio deployment to accept that I darn well should have permissions to perform this change.

     

    The root of the issue seems to be that CDC doesn't play well with .dbproj.  Meaning, when deploying changes (deltas) from a dbproj into a DB that already exists (in development), the deployment Fails when it cannot perform some ddl change on a table, because it says it is under CDC.  So, what I'm trying to do is disable CDC at both DB level and on each table in the pre-script and then re-enable it in the post.

     

    If you can recommend either where to go inside of the project file to ensure the correct permissions are being given AND/OR some other suggestion for how to work with CDC within the context of this dbproj I'll be quite grateful!


    Tim Carper
    Tuesday, May 25, 2010 3:08 PM
  • First off all you should allways disable CDC on database or at least on table level when doing schema changes. CDC does not play well with schema changes, even if in your case the schema change has been committed CDC would stop gathering data. CDC automatically stops gathering data after a schema change on the table (not sure what schema changes will trigger the stop).

    When doing schema changes on object monitored by CDC you should go something like this:

     

    1. Collect CDC data

    2. Disable CDC (table/database)

    3. Schema changes

    4. Data import

    5. Enable CDC (table/database)

     

    As for the dbproj I have not been using auto deployment feature, so I can't help you with implementing scripts into the deployment. As for the security privileges you should check via profiler which user is Visual Studio using for applying schema changes.

    Tuesday, May 25, 2010 3:25 PM
  • Ok this hole thing happend again, and re-enabling the cdc_cleanup_job did not work this time.

     

    So I dug around a bit and after I deleted all of the cdc objects and schema from my database SQL server still would not comply. So a couple of more hours of hair pulling until I came accross a table dbo.cdc_jobs in msdb database. Deleted all records from the database and the sys.sp_cdc_disable_db  stored procedure removed CDC as it should have in the first place.

     

    So it seams that deleting jobs from the msdb.dbo.cdc_jobs did the trick this time around.


    • Marked as answer by Luka Ferlež Tuesday, February 22, 2011 12:02 AM
    Tuesday, February 22, 2011 12:02 AM
  • The following code should allow you to disable cdc:

    use [<your db name>]

    DROP TABLE dbo.systranschemas



    Thursday, July 5, 2018 10:08 AM