SQL SERVER – Rollback TRUNCATE Command in Transaction

This is very common concept that truncate can not be rolled back. I always hear conversation between developer if truncate can be rolled back or not.

If you use TRANSACTIONS in your code, TRUNCATE can be rolled back. If there is no transaction is used and TRUNCATE operation is committed, it can not be retrieved from log file. TRUNCATE is DDL operation and it is not logged in log file.

Update: (Based on comments of Paul Randal) Truncate *IS* a logged operation, it just doesn’t log removing the records, it logs the page deallocations.

Following example demonstrates how during the transaction truncate can be rolled back.

Read more: Journey to SQL Authority with Pinal Dave

Posted via email from jasper22's posterous