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]'
Enjoy Reading This Article?
Here are some more articles you might like to read next: