Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sp_who2 BlkBy Sleeping Process Awaiting Command

When running sp_who2, it appears one of my SQL commands is blocking but waiting on a process that is "Sleeping" and "Awaiting Command". This doesn't make any sense.

alt text

Any ideas what might be causing this? I know the DELETE is running inside a transaction that previously inserted a lot of rows into the table, could that be the problem?

like image 545
Stefan Mai Avatar asked Nov 18 '10 19:11

Stefan Mai


1 Answers

You probably have an open transaction on SPID 98. A blocking SPID does not have to be active

Try this, look at the open_tran column

SELECT
    p1.SPID AS blockedSPID, p2.SPID AS blockingSPID, ...
FROM 
    master..sysprocesses p1
    JOIN
    master..sysprocesses p2 ON p1.blocked = p2.spid

Following on, this script gives you open transactions, last SQL and plan.

And have a read of KB 224453 for good measure

like image 109
gbn Avatar answered Nov 18 '22 12:11

gbn