4

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

5

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

| improve this answer | |
  • 2
    Depending on the database recovery model, extents modified by a bulk-logged operation like TRUNCATE TABLE may be tracked in the BCM. Such extents will not be reused until backed up. It is true the data cannot be read directly using fn_dblog but that is not the same as saying that reading the log could not recover the data. – Paul White Jan 3 '14 at 6:19
3

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

| improve this answer | |
  • 1
    Matan Yungman is correct. It logs the extents for the data that is truncated, allowing the truncated data to be restored if the containing transaction is rolled back. – Shane Estelle Dec 31 '13 at 17:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.