Rollback of a Transaction

Question

Rollback of a Transaction

chandan_jha18

SSChampion

Points: 11330
More actions
July 21, 2011 at 7:09 am

#240196

I was playing with few SQL statements today. For example:
begin tran
insert 10 rows
rollback tran
I begin the transaction, verify the changes in the table and then rollback them again using the rollback command in the same management studio query window.
Now what will happen if my system reboots or system crashes. I will open the management studio again and then how do i rollback that particular transaction. I can do a dbcc opentran but what if it shows more than 1 transactions and how do i know which one was mine.
Regards
Chandan

Viewing 10 posts - 1 through 9 (of 9 total)

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

Panders Right there with Babe Points: 772 More actions · Answer 1

It will rollback when the database starts up, check the SQL Server Log and there will be a message showing transaction rollback.

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

If the server crashes, all open transactions are rolled back on restart. It's called crash recovery.

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

anil_kumar32 Ten Centuries Points: 1378 More actions · Answer 3

[font="Times New Roman"]

When you restart your server instance, all the databases residing on that instance are recovered.

During recovery process all uncommitted transacations are rollbacked and all committed transactions are replayed. This process is called automatic recovery.

Let me know if i was wrong.

Regards,

Anil Kumar

[/font]

chandan_jha18 SSChampion Points: 11330 More actions · Answer 4

GilaMonster (7/21/2011)
If the server crashes, all open transactions are rolled back on restart. It's called crash recovery.

I meant to say that my workstation or management studio crashed. In that case, how do i find my transaction among other open transactions.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

DBCC Opentran will show the open transactions and using the SPID you can use

DBCC Inputbuffer (SPID Number)

Or you can use

select s.spid , [Text] as Code from sys.sysprocesses as a

cross apply sys.dm_exec_sql_text (s.sql_handle) as D where open_tran = 1

-Roy

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

chandan_jha18 (7/21/2011)
GilaMonster (7/21/2011)
If the server crashes, all open transactions are rolled back on restart. It's called crash recovery.
I meant to say that my workstation or management studio crashed. In that case, how do i find my transaction among other open transactions.

If your connection was closed (by your machine or SSMS crashing) any open transactions would be rolled back automatically.

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

chandan_jha18 SSChampion Points: 11330 More actions · Answer 7

Roy Ernest (7/21/2011)
DBCC Opentran will show the open transactions and using the SPID you can use
DBCC Inputbuffer (SPID Number)
Or you can use
select s.spid , [Text] as Code from sys.sysprocesses as a
cross apply sys.dm_exec_sql_text (s.sql_handle) as D where open_tran = 1

Ok.lets say that i get two open transactions and i can identify that transaction details by using dbcc inputbuffer. So how to rollback that tran. By killing that process spid?

chandan_jha18 SSChampion Points: 11330 More actions · Answer 8

GilaMonster (7/21/2011)
chandan_jha18 (7/21/2011)
GilaMonster (7/21/2011)
If the server crashes, all open transactions are rolled back on restart. It's called crash recovery.
I meant to say that my workstation or management studio crashed. In that case, how do i find my transaction among other open transactions.
If your connection was closed (by your machine or SSMS crashing) any open transactions would be rolled back automatically.

Ok. let me more it more interesting. I was logged on to my remote server using citrix or some dial-up connection. now power failure in my city for two days. I can call someone to login and find out my insert command using sp_who2. So how he will rollback my transaction? By killing the spid?

I am sure if he opens a query window and type rollback tran , then it won't work as for his session there was no corresponding begin tran.

Thanks for answering and sorry for my confused mind!

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

chandan_jha18 (7/21/2011)
Ok. let me more it more interesting. I was logged on to my remote server using citrix or some dial-up connection. now power failure in my city for two days. I can call someone to login and find out my insert command using sp_who2. So how he will rollback my transaction? By killing the spid?

If somehow your connection is still open through all that, then killing the connection is the same as closing the connection due to a crash - open transactions rolled back.

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