How to detect ORPHAN SPIDs in the database?

Question

Post reply

How to detect ORPHAN SPIDs in the database?

soumil

Mr or Mrs. 500

Points: 508
More actions
March 13, 2003 at 3:45 pm

#58920

How do we detect if there are any orphan SPID's holding resources at the database level.
For eg. in Oracle theres a process PMON which comes and cleans up the dead processes and it also releases all the locks held by the dead process.
I am experiencing the same thing where the DATABASE is holding up these locks indefinetly.
Any help on this topic is very well appreciated.
Thanks
-Soumil

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

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

gfahrlander SSCommitted Points: 1888 More actions · Answer 1

/*sp_who will return:

spid (system process id)

where spid 0 - 50 = MSSQL Reserved spid's

and spid >= 51 = User/application spid's

ecid (execution context id)

where 0 = parent thread

and 1,2,3...n = sub threads

status (process status)

loginname

blk = spid for blocking process

where 0 = no blocking process

and -2 = blocking orphan transaction

dbname = database used by process

cmd = SQL Server command executing for process

*/

USE master

EXEC sp_who

soumil Mr or Mrs. 500 Points: 508 More actions · Answer 2

Thanks for the info.

Does -2 inthe blocked always indicate an ORPHAN transaction or only in the case of Distributed Transaction?

Thanks

-Soumil

Nongporn Lilian SSC Rookie Points: 46 More actions · Answer 3

Does anyone know where orphan distributed transaction comes from?

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 4

I didn't think we had orphaned transactions in SQL. You can use dbcc opentran to find open transactions, but I thought that if the client dropped the server would rollback.

Minaz Amin SSChampion Points: 11052 More actions · Answer 5

Please check this link:

http://support.microsoft.com/kb/271509/en-us

How to resolve blocking problems that are caused by lock escalation in SQL Server

Minaz Amin

"More Green More Oxygen !! Plant a tree today"

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 6

The problem may be with any SQL DMO or sp_OA* code you may have... if you forget to close and drop connections, you may end up with a "connection leak".

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