Can I truncate a table that is participating in transaction replication?

Can I truncate a table that is participating in transaction replication?

If you think about it conceptually, the table exists within the publication database, and the table is expected to exist on any databases that are subscribing to the publication. SQL Server Replication works by monitoring the database transaction log for inserts, updates, and deletes on objects that are replicated, and by transmitting those changes to subscribers. Truncate is a minimally logged transaction that essentially empties the table, but leaves the schema intact.

So, if I try to truncate a table that is in a published database by running TRUNCATE TABLE TRADES, I get an error:

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

If I convert the truncate into a DELETE FROM TRADES statement, this works perfectly – and is replicated to any subscribers.

If I try the same TRUNCATE TABLE TRADES statement on a subscriber database, this also works perfectly.

Leave a Reply

Your email address will not be published. Required fields are marked *