TSQL Tuesday #11:Rolling Back TRUNCATE TABLE

re: TSQL Tuesday #11:Rolling Back TRUNCATE TABLE

James Luetkehoelter — Tue, 12 Oct 2010 21:39:54 GMT

A very wise and sagacious post as usual. I find the same thing (the misconsception amongst active DBAs) with TRUNCATE or minimally logged transactions altogether. Hey, that would make a great 2-3 hour talk. That or hobts (Sorry Kalen, couldn't resist - don't blacklist me!).

re: TSQL Tuesday #11:Rolling Back TRUNCATE TABLE

mbourgon — Thu, 14 Oct 2010 17:22:03 GMT

Ha! Awesome. I've actually used that before (doing a truncate within a transaction), stunned that it worked that way. Thanks for the reminder.

re: TSQL Tuesday #11:Rolling Back TRUNCATE TABLE

arjanf — Tue, 19 Oct 2010 20:38:42 GMT

Hi Kalen,

I did deliver some comment on a linked in discussion a while ago based on the same subject.

I believe that when you do a truncation on a table with an Identity(1,1) column in it the identity is kept out of the transaction. This means that you could mess up your identity when you use it by mistake on such table. Is this correct?

Regards,

Arjan

re: TSQL Tuesday #11:Rolling Back TRUNCATE TABLE

Dale — Wed, 27 Oct 2010 20:47:27 GMT

But if the transaction is committed, then you cannot roll it back, correct?

re: TSQL Tuesday #11:Rolling Back TRUNCATE TABLE

Kalen Delaney — Wed, 27 Oct 2010 23:19:04 GMT

Arjan... what do you mean by 'mess up? There is no way that part of a row could be 'out' of the transaction. When you truncate a table with identity, the identity is reset.

Dale... COMMIT is the opposite of ROLLBACK. If you do one, you can't do the other.

re: TSQL Tuesday #11:Rolling Back TRUNCATE TABLE

Paul Williams — Mon, 01 Nov 2010 18:11:27 GMT

Thank you for logging this. I never realized TRUNCATE TABLE could be rolled back.

re: TSQL Tuesday #11:Rolling Back TRUNCATE TABLE

Raj — Sat, 22 Sep 2012 08:08:48 GMT

Kalen,

suppose we have done commit truncate table ..but can we recover that trucate table through transactional logged backup ? as we can do that for deleted rows ...

re: TSQL Tuesday #11:Rolling Back TRUNCATE TABLE

Kalen Delaney — Sun, 23 Sep 2012 00:33:43 GMT

Hi Raj

We can use a log backup to recover any changes by just restoring to a time BEFORE the change took place, whether it's a delete, truncate or drop table, it doesn't matter. The restore does not actually undo anything.. it just brings the database back to a point before you removed the data.

~Kalen