TRUNCATE TABLE and ROLLBACK TRAN

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Craig Farrell » TRUNCATE TABLE and ROLLBACK TRAN

59 posts, Page 6 of 6

«««2 3 4 5 6

TRUNCATE TABLE and ROLLBACK TRAN

Rate Topic

Display Mode

Topic Options

Author

Message

BudaCli

Posted Thursday, August 02, 2012 7:07 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:16 AM
Points: 1,046, Visits: 575

Nice & e@zy

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

Post #1339126

kapil_kk

Posted Thursday, October 11, 2012 1:39 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:11 AM
Points: 1,130, Visits: 1,278

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?

Post #1371247

GilaMonster

Posted Thursday, October 11, 2012 2:02 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:14 AM
Points: 38,118, Visits: 30,403

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

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1371258

Hugo Kornelis

Posted Thursday, October 11, 2012 3:45 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 5,300, Visits: 7,244

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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Post #1371313

Dineshbabu

Posted Friday, January 11, 2013 2:50 AM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 10:36 PM
Points: 773, Visits: 464

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..

Post #1405844

cfradenburg

Posted Friday, January 11, 2013 11:42 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 10:51 AM
Points: 1,562, Visits: 1,932

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.

Post #1406211

Hugo Kornelis

Posted Friday, January 11, 2013 2:09 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 5,300, Visits: 7,244

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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Post #1406258

GilaMonster

Posted Saturday, January 12, 2013 12:59 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:14 AM
Points: 38,118, Visits: 30,403

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 2008, MVP
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

Post #1406316

Dineshbabu

Posted Wednesday, January 16, 2013 11:37 PM

Right there with Babe

Group: General Forum Members
Last Login: Wednesday, May 29, 2013 10:36 PM
Points: 773, Visits: 464

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..

Post #1408160

« Prev Topic | Next Topic »

59 posts, Page 6 of 6

«««2 3 4 5 6

Permissions