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”

Recently I was working on one project and client required to audit the data, means, insert / update / delete should be tracked on some important table. There are many ways you can capture these information (Audit Trail), I have written quite a few articles on this subject too but all those needs additional code to be written. Fortunately my client is using SQL Server 2008 R2 version so I don’t even need to write down any specific code to capture changed data as there is facility of CDC (Change Data capture) in SQL Server 2008.

Even before we look at CDC in details, make sure you have your SQL Server Agent is running as CDC in SQL Server 2008 will use SQL Server Agent to make audit trail for you. If your SQL Server Agent is not running at the moment, start it from control Panel->Administrative Tools-> Services. You can find “SQL Server Agent (YourInstanceName)”, just start this service and then you will be able to work on CDC.

create database SQLHub

GO

use SQLHub

go

Create Table ChangeDataCapture

(

      ID INT Identity(1,1)

      ,Name varchar(20)

)

GO

--enable CDC in SQLHub database

--this will create "cdc" schema in SQLhub database too

--along with "cdc" schema, it will create some system table

--in "cdc" schema

USE SQLHub

GO

EXEC sys.sp_cdc_enable_db

GO

--now enable CDC for our table created above.

--when you will enable cdc for ChangeDataCapture table

--it will create two job under SQL Server Agent

--which will read data from transaction whenever you will make any change in data

--and stores it in CDC table

USE SQLHub

GO

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name   = N'ChangeDataCapture',

@role_name     = NULL

GO

--now you have orginal table, named "ChangeDataCapture"

--another table to keep all changes "ChangeDataCapture",

--which has been created in step above

select * from ChangeDataCapture

--following table will be there under category of "System Tables" folder.

select * from cdc.dbo_ChangeDataCapture_CT

--now let us make some DML operation in ChangeDataCapture table and

--observe how does it stores data in cdc.dbo_ChangeDataCapture_CT

INSERT INTO ChangeDataCapture

SELECT 'Ritesh Shah' UNION ALL

SELECT 'Rajan Shah' UNION ALL

SELECT 'Teerth Shah'

GO

--see the data in both tables

select * from ChangeDataCapture

--in cdc.dbo_ChangeDataCapture_CT, you can see value 2 in _$operation field.

--2 represent INSERT.

select * from cdc.dbo_ChangeDataCapture_CT

--see effect of UPDATE now.

UPDATE ChangeDataCapture

SET Name='Rajan Jain' WHERE Name='Rajan Shah'

--see the data in both tables

select * from ChangeDataCapture

--in cdc.dbo_ChangeDataCapture_CT, you can see value 3 and 4 in _$operation field.

--3 represent value before UPdate and 4 represent new value after update.

select * from cdc.dbo_ChangeDataCapture_CT

--see effect of DELETE now

Delete From ChangeDataCapture WHERE ID=2

GO

--see the data in both tables

select * from ChangeDataCapture

--in cdc.dbo_ChangeDataCapture_CT, you can see value 1 in _$operation field.

--1 represent DELETE operation

select * from cdc.dbo_ChangeDataCapture_CT

BTW, if you wish to look at the other manual method of capturing table tracking as known as Audit Trail, have a look at following links:

http://www.sqlhub.com/2011/07/audit-trail-with-output-clause-in-sql.html

http://www.sqlhub.com/2009/03/auditing-trail-with-trigger-in-sql.html

http://www.sqlhub.com/2009/11/audit-in-sql-server-2008.html

http://www.sqlhub.com/2009/08/output-clause-in-sql-server-20082005.html

http://www.sqlhub.com/2009/03/keep-log-of-update-in-same-table-with.html

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”