July 14, 2003 at 8:28 am
I am seeing more than a couple of orphan transactions, but i am not 100% sure which orphan connection is blocking the legit process so i have been killing all orphan transactions that exists in the syslockinfo table.
Don't think there's such a thing as a good orphan transaction, but my peers are not comfortable with cleaning up all orphan transactions manually (instead of letting Windows OS does it job).
Any one has found a document or 100% sure that they have found a way?
slash
July 14, 2003 at 8:42 am
sp_who2 will tell you which transaction is blocking other transactions and which transactions are being blocked by who.
How do you know those transaction are prphan transactions?
July 14, 2003 at 8:52 am
sp_who2 tells me the blocker SPID is -2, so i get to the syslockinfo the get a list of all orphan transactions.
The question is which one if there are more than one?
slash
July 14, 2003 at 9:19 am
syslockinfo contains information on all granted, converting, and waiting lock requests. How do you interpret these information into a list of orphan transactions?
Are you talking spid 2 or spid -2?
July 14, 2003 at 9:27 am
-2 in the req_spid field.
I can't find any more information in the BOL other than brief description on each attribute in the syslockinfo table.
slash
July 14, 2003 at 9:34 am
I am not aware of spid -2. you should kill the block process from the finding of sp_who2 and find out what command is causing the block by running dbcc inputbuffer before you kill it.
July 14, 2003 at 9:47 am
allen,
thanks for your time.
fyi...SPID -2 is assign to all orphan connections, where the client has somehow disconnect from SQL Server and Windows abnormally.
slash
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply