What steps to follow, If there is an open transaction on Database server?

Question

What steps to follow, If there is an open transaction on Database server?

Ali Tailor

Hall of Fame

Points: 3769
More actions
March 15, 2011 at 5:12 am

#389049

Hi
Duds!!
Just sujest me
What steps to follow, If there is an open transaction on Database server?
Thanks in advance
Ali
MCTS SQL Server2k8

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

Step 1: Evaluate whether or not it is a problem. No sense in doing anything if there's no problem.

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

Lowell SSC Guru Points: 323518 More actions · Answer 2

well an open transaction is not a bad thing.

a running process could take a lot of time to finish; so in general, you do nothing at all when there is an open transaction.

now if you have a situation where it's friday night, a developer started a transaction in SSMS, ran something that is locking up some tables, and then went home without committing that offending transaction, you might want to do something.

1; if you were going to restore the database, so you have no problem kicking everyone out to do what you have to do,you can issue the command

ALTER DATABASE YourDataBase SET SINGLE_USER WITH ROLLBACK IMMEDIATE

2; if you want to just clear that one issue, and allow the rest of the users to continue, you want to run

exec sp_who2

/*

SPID Status Login HostName BlkBy DBName Command

57 SUSPENDED Stormdev\Lowell STORMDEV 56 SandBox SELECT

*/

your looking for any connections that are being blocked by open transactions...look for non-null values in the BlkBy column.

that is the specific spid that has the open transaction. in my example, you can see that spid [56]is blocking spid [57].

generally, you want to watch that for some period of time, by running the sp_who2 command multiple times.

you never want to kill a connection just because it is blocking someone...you need to make sure it's a real issue before you get the hammer out and treat every issue as if it were a nail.

if you are sure that the issue is real, you could then kill the connection for spid [56] which would automatically rollback that spids open transactions:

KILL 56

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ali Tailor Hall of Fame Points: 3769 More actions · Answer 3

Hi

Lowell, and All

It is what I was expecting From experts, thank you very much Lowell for a detail reply.

Ali
MCTS SQL Server2k8

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

Ali Tailor (3/15/2011)
Hi
Lowell, and All
It is what I was expecting From experts, thank you very much Lowell for a detail reply.

Ummm... they actually forgot to mention taking the offending user out for a nice pork chop dinner. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Jeff Moden (3/15/2011)
Ummm... they actually forgot to mention taking the offending user out for a nice pork chop dinner. 😛

Maybe, maybe not.

There's nothing intrinsically wrong with an open transaction. If it's open long enough to cause a problem, it could also be the developer who is to blame.

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

kchant Ten Centuries Points: 1231 More actions · Answer 6

I agree, as with all open transactions you should check to see what it is doing and if it is actually causing a blocking problem before deciding to kill it...