What does a TRUNCATE TABLE write to the Log file in SQL Server?

Posted on

Question :

I know it does not log everything like a DELETE would but I am not sure what it would write to the log file.

Answer :

There is a huge difference between the records logged in a transaction log file for a DELETE and for TRUNCATE TABLE statement

A DELETE statement records what exactly has been deleted, i.e. the value of the deleted row e.g. ‘JohnSmith’, so you can read the transaction log content (you can use fn_dblog), see what was deleted and re-insert the deleted record if you want.

With the TRUNCATE TABLE statement, you cannot do this, as the statement is ‘minimally logged’. The exact deleted values are not logged in the transaction log, only the IDs of the pages that held the truncated records are logged. These pages are marked for overwriting in the database data file and the truncated data will be gone for good when the new transactions are written to these pages.

Therefore, reading only the transaction log cannot provide the values that are lost due to the TRUNCATE TABLE statement. The only chance you have to recover the truncated records is to read the page ID in the transaction log and find it in the MDF file, in case it hasn’t been overwritten

Generally speaking, it writes the extents that it had deleted.
Check this article for more details:

http://www.sqlperformance.com/2013/05/sql-performance/drop-truncate-log-myth

Leave a Reply

Your email address will not be published.