Saturday, June 25, 2011

Which can be rolled back - DELETE or TRUNCATE ?

Refer  http://blog.sqlauthority.com/2007/12/26/sql-server-truncate-cant-be-rolled-back-using-log-files-after-transaction-session-is-closed/



If DELETE or TRUNCATE are enclosed within a TRANSACTION, then both can be rolled back.


DELETE can be recovered from log file always if full recovery mode is set for database. TRUNCATE may or may not be recovered always from log files.

because : In case of TRUNCATE, SQL Server deallocates the data files in the table and records deallocation of the data files in the log files.

No comments:

Post a Comment