Msg 22926, Level 16, State 1, Procedure sp_cdc_verify_capture_instance, Line 36 Could not create a capture instance because the capture instance name ‘dbo_ChangeDataCapture’ already exists in the current database. Specify an explicit unique name for the parameter @capture_instance.

After reading my previous article on CDC(Change Data Capture), one of the reader had tried to do it in hisdevelopment server but in his server, CDC was already enabled and anotherco-incident was that, the table he has choose for CDC, was already having itscapture instance, may be any of the other team member might have done it and hedoesn’t knew that , so he was greeted with the error like given below.
Msg 22926, Level 16, State 1, Procedure sp_cdc_verify_capture_instance,Line 36
Could not create a capture instance because the capture instance name’dbo_ChangeDataCapture’ already exists in the current database. Specify anexplicit unique name for the parameter @capture_instance.
Error message is pretty much clear that the table he was usingwas already having capture instance so obviously he should use another tablefor this testing purpose or remove CDCfrom that table and re-create it or while activating CDCfor that table, he has to provide another name for capture instance table by providing@Capture_Instance name explicitly (not recommended). I will provide TSQL neededfrom disable CDClater in this article.
Well, these are some of the solution when you face abovegiven error message but as said by someone that “Prevention is better than cure”, that reader asked me how do I knoweven before activating CDCthat the table is CDC enabled or not. 
It is not a big issue; you can use very small TSQL querieslike below when you want to know it.
–list out thename of databases which are CDC enabled
SELECT [name], database_id
FROM master.sys.databases WHERE  is_cdc_enabled =1    
GO
–list out alltables which are CDC enabled in your database
SELECT [name] AS Table_name
FROM sys.tables  WHERE is_tracked_by_cdc =1
GO
–know indetails like which table is CDC enabled
–which is thecapture instance of that table
EXEC sys.sp_cdc_help_change_data_capture
GO
–disable CDCfrom your table.
EXECUTE sys.sp_cdc_disable_table
    @source_name = N’ChangeDataCapture’,
    @source_schema =N’dbo’,
    @capture_instance =N’dbo_ChangeDataCapture’;
   
–disable CDCfrom your database
EXEC sys.sp_cdc_disable_db
GO
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: