SQLServerPedia

Few days ago, I got the great opportunity to visit Bangalore Infosys. Please read the complete details for the event here: SQLAuthority News – Notes from TechDays 2009 at Infosys, Bangalore. I mentioned during the session that CDC is asynchronous and it reads the log file to populate its data. I had received a very interesting question during the session.

The question is as follows: does CDC feature capture the data during the truncate operation? Answer: It is not possible or not applicable. Truncate is operation that is not logged in the log file, and if one tries to truncate the table that is enabled for CDC, it will right away throw the following error.

Msg 4711, Level 16, State 1, Line 1
Cannot truncate table because it is published for replication or enabled for Change Data Capture.

Let us create the scenario that will generate the above error.

/***** Set up TestTable *****/

USE AdventureWorks

GO

-- Create Table

CREATE TABLE dbo.TestTable (ID INT)

GO

-- Insert One Hundred Records

-- INSERT 1

INSERT INTO dbo.TestTable (ID)

SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.name) RowID

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

/***** Enable CDC *****/

--The following script will enable CDC in AdventureWorks database.

USE AdventureWorks

GO

EXEC sys.sp_cdc_enable_db

GO

--Following script will enable CDC on dbo.TestTable table.

USE AdventureWorks

GO

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name = N'TestTable',

@role_name = NULL

GO

/* Attempt to Truncate Table will thrown following error

Msg 4711, Level 16, State 1, Line 1

Cannot truncate table 'TestTable' because it is published for replication or enabled for Change Data Capture.

*/

TRUNCATE TABLE TestTable

GO

/***** Clean up *****/

--Disabling Change Data Capture on a table

USE AdventureWorks;

GO

EXECUTE sys.sp_cdc_disable_table

@source_schema = N'dbo',

@source_name = N'TestTable',

@capture_instance = N'dbo_TestTable';

GO

--Disable Change Data Capture Feature on Database

USE AdventureWorks

GO

EXEC sys.sp_cdc_disable_db

GO

-- Drop Table

DROP TABLE TestTable

GO

The workaround for this is to either use DELETE statement instead of TRUNCATE or to disable CDC first and then enable the CDC on the table after the TRUNCATE operation is completed.

I am interested to know if you have any interesting fact of above CDC. I will publish the information on this blog with due credit.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Error Messages, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLAuthority Author Visit, SQLServer, T SQL, Technology Tagged: CDC