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
