Processes Blocking on SQL servers

1.    Connect SSMS on alerting sql server and run below to verify existing blocking on this server. If you see multiple blocking SPID ‘s on this server  and not changing their number then proceed to next step else close the ticket

SELECT * FROM sys.sysprocesses WHERE blocked <>0

2.    Verify if  index rebuild, index reorg or updatestats jobs are running on sql server . If exits stop the job.

3.    Wait for 2 mins again run below query to verify current blocking SPID’s If there are no blocking SPID’s then close ticket.

SELECT * FROM sys.sysprocesses WHERE blocked <>0

4.    If we still have blocking on this server and there are no sql jobs are running then proceed to next step.

5.    Run below to query to identify head blocker SPID , its duration , who is running this query and from which server query is running 

select spid,cast (RIGHT(CONVERT(VARCHAR,DATEADD(ms, DATEDIFF(ms, last_batch, GETDATE()), '1900-01-01'), 121), 12) as time(3))duration,loginame ,hostname from sys.sysprocesses where spid in (Select distinct blocked from sys.sysprocesses ) and blocked =0

6.    If you find SPID is running more than 15 mins and that SPID is running any individual user then run below DBCC command by providing SPID id to check what query user executing.

                             DBCC INPUTBUFFER (<<SPID>>)

7.    If user is running select query Kill that SPID and Verify If we have blocking on  this server.