TRUNCATE TABLE and ROLLBACK TRAN

Question

TRUNCATE TABLE and ROLLBACK TRAN

Viewing 14 posts - 46 through 58 (of 58 total)

You must be logged in to reply to this topic. Login to reply

Revenant SSC-Forever Points: 42467 More actions · Answer 1

David Burrows (2/8/2012)
GilaMonster (2/6/2012)
Given the distribution of answers, I'm considering a followup question - "does recovery model ever affect the ability to rollback transactions?" because it appears way too many people don't understand what recovery models do.
Or maybe create MotD (Myth of the Day) 😀

They seem to pop up on their own, without anyone really trying.

TomThomson SSC Guru Points: 104773 More actions · Answer 2

GilaMonster (2/6/2012)
Is it bad form, if you create a QotD, to list your own article as a reference?

depends who does it. It won't be bad form if you do it.

Tom

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 3

Good question. I missed it but learned something.

http://brittcluff.blogspot.com/

Kwex Default port Points: 1407 More actions · Answer 4

Kwex

Default port

Points: 1407

February 17, 2012 at 8:01 am

#1448531

Good question ... thanks!

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 5

Jamie-2229

SSCrazy Eights

Points: 8156

March 31, 2012 at 2:27 pm

#1467343

Nice to test and show answer as absolute.

Jamie

BudaCli Hall of Fame Points: 3384 More actions · Answer 6

Nice & e@zy

What you don't know won't hurt you but what you know will make you plan to know better

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

Ahhh... 🙁

I thought Recovery mode has effect while rolling back the transaction.

well new thing to learn today...

Can any give an example of this?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Recovery model has no effect ever on rolling back a transaction.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 9

kapil190588 (10/11/2012)
Can any give an example of this?

Yesterday, when you asked for resources to learrn about SQL Server, one of the suggestions I gave (the first, to be precise) is to make sure you have an instance of SQL Server that you can use to play around with. Now is the time to use that instance.

It is really very easy to create this example yourself. Just create a table, put in a few rows of data, then start a transaction, truncate the table, rollback the transaction and then check the contents of the table.

That example is so basic, that just typing and posting the code would probably have taken me less time than it took me to write this message. But I hope this message has the higher educational value.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 10

Since I worked only in 2005 & above i think got it correct. I don't have idea about earlier versions.

As far as I know anything written between

BEGIN TRAN

.

ROLLBACK

can be rolled back.

Please correct me if i'm wrong.

--
Dineshbabu
Desire to learn new things..

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 11

Inserts into a table variable aren't rolled back. "SELECT NEXT VALUE FOR <sequence>" isn't rolled back either. I'm not aware of anything else that isn't but there's likely a small number that aren't.

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 12

Not just inserts into a table variable, all modifications to table variables are retained after a rollback.

Other things that are not rolled back are assignments to scalar variables, identity values that were givven out are not given out again (so you can get gaps in the identity sequence), and you can still observe the effects of a rolled back transaction in most dynamic management views.

On a more technical side, modifications written to the transaction log are not removed on a rollback (there is in fact an extra entry added, to log the rollback event). And I think (but have not tested) that an autogrow of the database file that takes place because of a huge insert that is rolled back will not be undone.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Page splits don't roll back.

Also, while table variables ignore user transaction rollbacks, statements will still automatically roll back in the case of an error

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 14

Thanks cfradenburg, Hugo & GilaMonster.

I will take the TSQL part, Changes made to Table variables and Scalar variables will not be affected by Rollback. As well as Identity value will not be re-seeded.

--
Dineshbabu
Desire to learn new things..