A Haunting TSQL Tuesday Tale

Comments: 2 Comments
Published on: October 11, 2010

Time for the ghouls and goblins to come out of the woodwork once again for another tale of deception and tyranny.  OK maybe not tyranny but definitely a haunting tale is to be told this month while trick-or-treaters plan their routes and mend their costumes.  We have SQueeLing experts suiting up for the adult party known as TSQL Tuesday.

This month the vampires, ghosts, and zombies are all breaking it down at Sankar Reddy’s place (Blog | Twitter).  The ghoulish stories to be shared are to be myths from the underSQLworld.

Truncating a Table is Unrecoverable

Have you heard this one in the past?  If you truncate a table, you cannot undo the action like you can with a delete.  What is the difference between the two methods to remove data from a table?

When you truncate a table, the pages are deallocated with minimal locking of the resources.  When you use the delete statement, the data is deleted row-by-row with more locks.  Everybody knows that a delete can be rolled back.  Many people believe that a Truncate cannot be rolled back.

Well, at this point we really need a table to test.  So let’s create a table and perform a few simple tests.

With the test table ready to go, we will begin with the baseline demonstration on the Delete command.  Here is the script to demonstrate that a Delete can be rolled back (but we already knew that).

Pretty simple and straight forward – baseline is proven with that script.  The premise that a Delete can be rolled back has been established.  Now, how about that truncate statement?  For the truncate statement, I will use the same simple script, substituting the appropriate commands in this script.

When this script is executed, we see that indeed the data is recoverable.  Here are the results I get from the truncate and associated rollback script.


I think this is one of those myths that is frequently floated throughout the SQL world due to another myth that is related to the Truncate statement.  It is often explained that you can’t recover a truncate because the truncate is not logged.  That is also obviously wrong (if it weren’t logged, how could you recover the table without a restore?).  The myth about Truncate being non-logged has already been debunked (as has this one – in a different series) by Paul Randal in his Myth-A-Day series.  The key to this is that the Truncate is logged and that you wrap the command in a transaction – and only commit when you are ready.

This happens to be a good post for the back to basics series. Other topics in the series include (but are not limited to): Backupsbackup history and user logins.

2 Comments - Leave a comment
  1. […] Jason debunks one of the oldest myths in SQL Server that TRUNCATE is NOT logged with a simple script. […]

  2. […] My Article: A Haunting TSQL Tuesday Tale […]

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

October 2010
« Sep   Nov »

Welcome , today is Sunday, March 29, 2020