CDC and TRUNCATE TABLE

[ousoonerjoe]

Using SQL 2008 R2.

Apparently the CDC auditing process does not like TRUNCATE TABLE. Normally, a DELETE statement can get around this, but when using the Import/Export wizard, it fails. The CDC blocks the ability to TRUNCATE the table and causes the import to fail.

Has anyone else run into this problem? I haven't been able to locate a fix or work around to resolve it.

Is there a way to "Pause" the CDC long enough to allow a TRUNCATE TABLE to execute?

Any suggestions are welcome.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[ousoonerjoe]

Found another article that describes the same scenario with examples. As you see, there is no resolution there either.

SQL SERVER – CDC and TRUNCATE – Cannot truncate table because it is published for replication or enabled for Change Data Capture

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[ousoonerjoe]

UPDATE: Installed the emergency patch for the CDC issues. While it fixes the Rebuilding of Indexes, it still does not allow for TRUNCATE TABLE if CDC is enabled on it.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------