Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456

TRUNCATE TABLE and ROLLBACK TRAN Expand / Collapse
Author
Message
Posted Thursday, August 2, 2012 7:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 6:08 AM
Points: 1,079, Visits: 591
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
Posted Thursday, October 11, 2012 1:39 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1371247
Posted Thursday, October 11, 2012 2:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
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
Posted Thursday, October 11, 2012 3:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 5,967, Visits: 8,220
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
Posted Friday, January 11, 2013 2:50 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 4, 2014 5:38 AM
Points: 945, Visits: 558
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
Posted Friday, January 11, 2013 11:42 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 4, 2014 8:10 AM
Points: 1,635, Visits: 1,972
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
Posted Friday, January 11, 2013 2:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 5,967, Visits: 8,220
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
Posted Saturday, January 12, 2013 12:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:36 AM
Points: 42,765, Visits: 35,863
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
Posted Wednesday, January 16, 2013 11:37 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 4, 2014 5:38 AM
Points: 945, Visits: 558
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 »

Add to briefcase «««23456

Permissions Expand / Collapse