SQL Head blocker information

Following are the scripts which can be used to identify head blocking sessions .

script 1:

--create alert to run a job when blocked sessions are more than 200

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'processes blocked',
  @message_id=0,
  @severity=0,
  @enabled=1,
  @delay_between_responses=900,
  @include_event_description_in=0,
  @category_name=N'[Uncategorized]',
  @performance_condition=N'SQLServer:General Statistics|Processes blocked||>|200',
  @job_id=N'give jobid of following job'
GO

/*create job which will run following script . use the job id of the job as parameter in above mentioned @job_id field */

SET CONCAT_NULL_YIELDS_NULL OFF

SELECT spid,hostname,program_name,blocked,loginame FROM master.dbo.sysprocesses with (nolock)
 WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)

 if @@ROWCOUNT >0
 begin
DECLARE @Body VARCHAR(8000) ='<br><br> Blocked sessions count is more than 200  Following are the head blocker sessions <br>   <br> <table border=1><tr><td>spid</td><td>status</td><td>program_name</td><td>login_name</td><td>host_name</td><td>open_tran</td><td>databasename</td><td>cmd</td><td>lastbatch</td><tr>'     
SELECT
 spid, status, program_name, loginame,
 hostname,
  blocked,
 open_tran,

  db_name(dbid) as databasename,cmd,
 
  last_batch into #temptable
 FROM master.dbo.sysprocesses
 WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
 AND blocked=0      
Declare @spid int , @status nvarchar(20) ,@programname nvarchar(128), @login nvarchar(128), @hostname nvarchar(128),@opentran smallint,@databasename nvarchar(20),@cmd nvarchar(20), @lastbatch datetime
       DECLARE headblock_Cursor CURSOR LOCAL FOR
              SELECT spid,status,program_name,loginame,hostname,open_tran,databasename,cmd,last_batch FROM #temptable
              OPEN headblock_Cursor;
              FETCH NEXT FROM headblock_Cursor INTO @spid,@status,@programname,@login,@hostname,@opentran,@databasename,@cmd,@lastbatch;
              WHILE @@FETCH_STATUS = 0
                 BEGIN
                           SET @Body = @Body + '<td>' + CONVERT(VARCHAR,@spid) + '</td>'
                           SET @Body = @Body + '<td>' + CONVERT(VARCHAR,@status) + '</td>'
         SET @Body = @Body + '<td>' + CONVERT(VARCHAR(128),@programname) + '</td>'
                           SET @Body = @Body + '<td>' + CONVERT(VARCHAR,@login) + '</td>'
                           SET @Body = @Body + '<td>' + CONVERT(VARCHAR,@hostname) + '</td>'
                           SET @Body = @Body + '<td>' + CONVERT(VARCHAR,@opentran) + '</td>'
                           SET @Body = @Body + '<td>' + CONVERT(VARCHAR,@databasename) + '</td>'
                           SET @Body = @Body + '<td>' + CONVERT(VARCHAR,@cmd) + '</td>'
      SET @Body = @Body + '<td>' +CONVERT(NVARCHAR(max),@lastbatch) +'</td>'
                           SET @Body = @Body + '</tr>'
                       FETCH NEXT FROM headblock_Cursor INTO @spid,@status,@programname,@login,@hostname,@opentran,@databasename,@cmd,@lastbatch;
                 END;
              CLOSE headblock_Cursor;
              DEALLOCATE headblock_Cursor;
     drop table #temptable
       SET @BODY = @BODY + '      </tr>              </table>              <br>              <br> '
    EXEC msdb.dbo.sp_send_dbmail @recipients='someone@domain.com;alternate@domain.com',
    @subject = 'winsqla10 blocking header',
    @body = @body,
    @body_format = 'HTML' ;
end


script 2 :

--run following to get head-blocker queries in SQL , it has sql text info as well

SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH INTO #T
FROM SYS.SYSPROCESSES R CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS (
SELECT SPID, BLOCKED, CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL, BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
  AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,   R.BLOCKED, CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL, R.BATCH FROM #T AS R
   INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N'       ' + REPLICATE (N'|      ', LEN (LEVEL)/4 - 2) + CASE WHEN (LEN (LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END + CAST (SPID AS NVARCHAR (10)) + ' ' + BATCH AS BLOCKING_TREE FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO