sp_who2 BlkBy Sleeping Process Awaiting Command

Question

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.

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?

Answer 1

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

Answer 2

Without knowing the SQLs you are running it would be extremely difficult to say. But based on the Delete/Insert scenario, is it not possible that you have started a transaction for insert and then you have opened another transaction to delete some of those inserted records without comminting previous transaction?

I have seen this kind of issue before and this is how it manifests itself.