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 (https://blog.sqlauthority.com)
16 Comments. Leave new
Its really good concept
Dog there is one more flaw in CDC. If you try to update primary key of the source table which is being cdc-ed then it gets published as delete and insert and not just the update in to that cdc table. I’ve contacted Microsoft about this and they said it’s by “DESIGN”!
I know this is a really old thread, but what would be the problem with recording the update as a delete/insert for the purposes of CDC? Primary keys really are a special case.
hi all.
we use cdc tables for store modified data in database and we can see those easily,but now we want to add host name
and computer’s IP which modify data ,to cdc table as new field example:changed_byhost,changed_byip.is there any way for
add this field to cdc tables?
If you disable CDC and then re-enable, then you lose existing CDC tables that tracked changes up to the point of disabling CDC. How can you disable future CDC but keep already tracked data?
Is there is any process for truncating the tablle which are published in the replication?? Becoz i am having more than 350 tables which are being used fore generating crystal reports . should have to truncated before running the report.. I am having 5 branches data collection so have to make publication of 5branches.. kindly give me the solution so that my table should be truncated…
Sir i have question regarding that,
If we disable CDC and then re-enable, then we lose existing CDC tables that tracked changes up to the point of disabling CDC. How can we use both feature disable CDC but keep already tracked data?
Thx in advance …pls ans ASAP.
Have you got the reply for the question asked?
no
Truncate is most certainly logged. If it were not, no database that had a table truncated could ever be restored and recovered. Log shipping and mirroring would break as well as any other feature that depends on the transaction log or transaction log backups.
Don’t believe me?: Create a database, create and populate a table, take a full backup, truncate the table, table a log backup. Now restore the full backup with norecovery, then the log backup with recovery. Query the table and you’ll find it has no rows. The truncate was logged.
There must be some other reason why Microsoft doesn’t allow truncate along with CDC or CT but its not because the operation isn’t logged. It is.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log. So less transaction log space is used by the Truncate command.
how to maintain success message into sql server
plz help me
i am maintaining error log table
Hi Mr. Pinal, I was encoutered a strange act for enabling cdc for second table after I enabled it on the first without any problem: in the second table it does not catpture any change on the table when there a lot of changing, an when I executed “sys.sp_cdc_help_change_data_capture” I surprised by “source_schema,
source_table” are null and “index_column_list” listed only one Column when my Key is formed by 2 columns, why this happened, please Help! and Thanks
Hello,
Couple of questions:
1. I need to find out which tables (out of about 500) change when our application makes setup changes.
I turned on CDC for all tables in my DB, now I need to find which tables have changes.
? Is there a way or table that will show me which tables changed w/o me having to select every CDC.CT_* table?
2. Is there a way to have CDC work w/o owner being changed to ‘sa’ OR can I have two owners in SQL Server?
Thanks in advance!
TRUNCATE is logged, it is a common misconception that it isn’t.
Proof: “BEGIN TRANSACTION; TRUNCATE TABLE xy; ROLLBACK” – this would not work if truncate was not logged into transaction log.
Why CDC/replication do not allow truncate to propagate? Because they all read transaction log (log reader agent) to pick up deleted rows and fill log tables (cdc tables or distribution database tables in the case of replication) with deleted rows. Then replication could apply deletion of rows on subscriber gradually.
Since in transaction log are not all deleted rows but only a truncation event (deallocation of pages), it has no rows to copy, so MS forbid truncate if table is published for replication or CDC. MS probably COULD do it in some way, at least for replication, but they didn’t.
Solution to TRUNCATE for replicated table is this:
1) set publication to allow partition SWITCH
2) Create empty table with same schema (SELECT * INTO dbo.temp FROM dbo.MyTable)
3) Create clustered index on empty temp table, matching original table (a requirement for switch)
4) Switch rows from published table into empty table:
ALTER TABLE dbo.MyTable SWITCH PARTITION(1) WITH dbo.temp
Now rows INSTANTLY appeared in temp table and dissapeared from the original table.
5) Drop temp table:
DROP TABLE dbo.temp
That process works (verified!) on table published for replication and has the same effect as TRUNCATE. Works for both partitioned and not-partitioned tables, just SWITCH statement is a bit different.