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]'