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.

Wednesday, July 20, 2011 |


After reading my previous article on CDC (Change Data Capture), one of the reader had tried to do it in his development server but in his server, CDC was already enabled and another co-incident was that, the table he has choose for CDC, was already having its capture instance, may be any of the other team member might have done it and he doesn’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 an explicit unique name for the parameter @capture_instance.

Error message is pretty much clear that the table he was using was already having capture instance so obviously he should use another table for this testing purpose or remove CDC from that table and re-create it or while activating CDC for that table, he has to provide another name for capture instance table by providing @Capture_Instance name explicitly (not recommended). I will provide TSQL needed from disable CDC later in this article.

Well, these are some of the solution when you face above given error message but as said by someone that “Prevention is better than cure”, that reader asked me how do I know even before activating CDC that the table is CDC enabled or not. 

It is not a big issue; you can use very small TSQL queries like below when you want to know it.

--list out the name of databases which are CDC enabled
SELECT [name], database_id
FROM master.sys.databases WHERE  is_cdc_enabled =1    
GO

--list out all tables which are CDC enabled in your database
SELECT [name] AS Table_name
FROM sys.tables  WHERE is_tracked_by_cdc =1
GO


--know in details like which table is CDC enabled
--which is the capture instance of that table
EXEC sys.sp_cdc_help_change_data_capture
GO

--disable CDC from your table.
EXECUTE sys.sp_cdc_disable_table
    @source_name = N'ChangeDataCapture',
    @source_schema = N'dbo',
    @capture_instance = N'dbo_ChangeDataCapture';
   
--disable CDC from 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 but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile

1 comments:

Unknown said...

SELECT [name], database_id
FROM master.sys.databases WHERE is_cdc_enabled =1

Table_name
VODResourcesPackageMap
VODResourceDetails

EXEC sys.sp_cdc_help_change_data_capture

source_schema source_table capture_instance object_id source_object_id start_lsn end_lsn supports_net_changes has_drop_pending role_name index_name filegroup_name create_date index_column_list captured_column_list
dbo VODResourceDetails dbo_VODRESOURCEDETAILS 267147997 1205579333 0x0000233F00001F320001 NULL 0 NULL CDC_Role NULL CDC_FileGroup 2014-01-27 15:31:28.267 NULL [ResourceId], [ProviderAssetId], [Type], [HD], [ScreenFormat], [Culture], [Content], [BranchId], [EffectiveDate], [Active]
NULL NULL dbo_VODRESOURCEGENRE 347148282 1189579276 0x0000233F00001F320001 NULL 0 NULL CDC_Role NULL CDC_FileGroup 2014-01-27 15:31:28.437 NULL [ReportingInsertDate], [ResourceId], [Genre], [BranchId]
dbo VODResourcesPackageMap dbo_VODRESOURCESPACKAGEMAP 427148567 210099789 0x0000233F00001F320001 NULL 0 NULL CDC_Role NULL CDC_FileGroup 2014-01-27 15:31:28.687 NULL [PackageId], [ResourceId], [BranchId], [EffectiveDate], [Active]

How do you delete the bad record?