12

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?

Stefan Mai
  • 23,367
  • 6
  • 55
  • 61

2 Answers2

14

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

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 1
    This was an issue with SSRS not reusing the connection that I opened the transaction with (thus never closing it), which ultimately meant that there was an open transaction holding down a lock on the table. Thanks for the help! – Stefan Mai Nov 19 '10 at 00:42
0

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.

Aliostad
  • 80,612
  • 21
  • 160
  • 208