Brandie Tarvin (4/23/2010)
SwaroopRaj (4/23/2010)
Truncates are *minimally logged* (EDIT: in ALL database Recovery models), like having your database in Bulk-Logged Recovery Model. That means there are pointers to the pages of the just removed data that can yank that stuff back if needed.
If I understand all the mexchanisc correct, then the actual TRUNCATE is done by deallocating entire pages. The log file will contain only the fact that page so-and-so was deallocated, but the log backup (if one is taken) will also include a copy of that page. So the pages that were deallocated are not available for reuse until the tran log has been backed up.
Deletes are logged more than Truncates, (EDIT: being fully logged in FULL mode down to the row) even in Bulk-Logged mode, because I think (and I could be wrong here) the pointers are more finite, pointing to the actually extents instead of the pages.
This is not quite correct. A DELETE processes individual rows. Each row deleted gets an entry in the log file, in ALL recovery models (even simple - otherwise, SQL Server would be unable to rollback or to recover after a crash). And all those entries are also written to the log backup.
So, yes, a Truncate can absolutely be rolled back. In fact, I'd be hard pressed to say what data change (not schema change) couldn't be rolled back at all.
One that has already been committed?:Whistling:
Seriously, I agree. In fact, even most schema changes can be rolled back.