CODEWITHSUNDEEP
sqlsql-server-2005
Cat
Cat

Reputation: 103

Cancelling query during truncate

I wanted to know what happens if i cancel query execution during truncate, are the records deleted? And is there any way to restore them? Truncate was activated on table which contained 1,5 million records From a little search in google i understand that the answer is no, but i wanted to be sure. I'm using SQL Server 2005

Upvotes: 2

Views: 1518

Answers (2)

Diego
Diego

Reputation: 36156

Good explanation on this link

As I understand, if you have a transaction control, both DELETE and TRUNCATE can be rolled back.

The difference is, without a transaction, if you do a DELETE you can still recover your data with a restore of the log for example, what you cant do on a TRUNCATE, because the delete will will be logged row-by-row and the truncate, as @JNK pointed out, is minimally logged.

Upvotes: 0

JNK
JNK

Reputation: 65187

Truncate deallocates the pages assigned to a table.

This is still atomic, in that you can cancel and have it rollback. You can also enclose a TRUNCATE in an explicit transaction and perform a rollback.

Once you commit the transaction, though, the records are gone. Unlike a DELETE, since the action is minimally logged, there is no record in the transaction log of the record contents.

Upvotes: 3

Related Questions