THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

SQL Server 2008 Change Data Capture: Msg 22830, Level 16, State 1

I was working with Change Data Capture this weekend and I happened upon a interesting problem while configuring the AdventureWorks2008 database for CDC.  I was working on my laptop in a disconnected state from the Active Directory Domain that my windows account belongs to.  When I ran the sys.sp_cdc_enable_db stored procedure to enable CDC, I received the following errors from SQL Server:

 

Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 186
Could not update the metadata that indicates database AdventureWorks2008 is enabled for Change Data Capture. The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 15404: 'Could not obtain information about Windows NT group/user 'DOMAIN\USERNAME', error code 0x6ba.'. 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_enable_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_enable_db, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

 

At first I thought it was that I was using my windows login account disconnected, and I would get by this by simply logging in a a SQL Login.  No dice, I got the same errors.  Then I took a look at the database owner, and it was the same Windows Login, so I changed it to a SQL Login and I was able to continue my tests with CDC. 

I am not sure how likely this exact type of scenario might be in a production SQL Server implementation, but I can foresee having a DBA create a Database and not pay attention to the fact that the database was configured with their account as the database owner.  Then if that DBA leaves an organization, their AD account would be removed/disabled, and a new DBA could encounter this issue trying to setup CDC.

Published Sunday, February 01, 2009 8:56 AM by Jonathan Kehayias
Filed under:

Comments

No Comments
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement