Some helpful queries used to do lock/running query diagnosis.

sp_who and sp_who2 system stored procedures returns some helpful information for diagnosing current connections; but it’s hard to filter. Here’s a quick query for dumping the results into a temp table for filtering. The key field is “status” to help you determine if the connection is hung.

DECLARE @Table TABLE(
 SPID INT,
 Status VARCHAR(MAX),
 LOGIN VARCHAR(MAX),
 HostName VARCHAR(MAX),
 BlkBy VARCHAR(MAX),
 DBName VARCHAR(MAX),
 Command VARCHAR(MAX),
 CPUTime INT,
 DiskIO INT,
 LastBatch VARCHAR(MAX),
 ProgramName VARCHAR(MAX),
 SPID\_1 INT,
 REQUESTID INT
)

DELETE FROM @Table
INSERT INTO @Table EXEC sp\_who2

SELECT \*
FROM @Table
WHERE DBName --...

In my case, we discovered it was a SQL Server Agent connection and it was running a job that was hanging. Some additional helpful queries to answer What is the Job Id? What are the current job statuses?

SELECT \*
FROM msdb..sysjobs s 
LEFT JOIN master.sys.syslogins l 
 ONs.owner\_sid = l.sid

EXEC msdb.dbo.sp\_help\_job @Job\_name = '[..]'

Stop a job:

EXEC msdb.dbo.sp\_stop\_job @Job\_name = '[..]'

Finally, a query to request last time a stored procedure was changed:

SELECT \*
FROM sys.objects
WHERE type = 'P' 
AND NAME = '[Stored Procedure Name]'