I have used this to email and/or automatically kill long blocking processes. Create a SQL Agent job to run this proc every 2 min.
CREATE PROC [dbo].[usp_EmailLongQueries]
AS
SET NOCOUNT ON
-- Make sure dbmail is started - sometimes it stops unexpectedly
EXEC msdb..sysmail_start_sp
DECLARE @emaildba varchar(255), @subject varchar(256), @body varchar(max), @killcmd INT
SET @emaildba = '******@nowhere.com'
DECLARE @servername varchar(256), @loginid varchar(256), @ntdomain varchar(100), @ntusername varchar(100),
@hostname varchar(255), @hostproc varchar(10), @spid varchar(10), @execcont varchar(10), @waittype varchar(256),
@waitresource varchar(255), @waittime varchar(10), @blockedby varchar(10), @starttime datetime, @runtimesecs varchar(10),
@runtimemin varchar(10), @status varchar(20), @dbname varchar(255), @commandtype varchar(25), @sqllen int, @sqlcmd varchar(max),
@progname varchar(255), @blkcnt int
DECLARE longquery CURSOR FOR
select
@@SERVERNAME as ServerName,
sproc.loginame AS LoginID,
sproc.nt_domain AS NTDomain,
sproc.nt_username AS NTUserName,
sproc.Hostname AS HostName,
sproc.hostprocess,
sproc.spid Session_ID,
sproc.ecid Execution_Context,
sproc.lastwaittype AS Wait_Type,
sproc.waitresource AS Wait_Resource,
sproc.waittime AS Wait_Time,
CASE WHEN sproc.blocked = 0 THEN 0 ELSE sproc.blocked END as BlockedBy,
sproc.last_batch Started_At,
datediff(second,sproc.last_batch,getdate()) Elapsed_Seconds,
datediff(mi,sproc.last_batch,getdate()) Elapsed_Mins,
sproc.status Status,
DB_NAME(sproc.dbid) AS DBName,
sproc.cmd Command,
len(sqltext.TEXT) SQL_Length,
ISNULL(SUBSTRING(sqltext.text, (sproc.stmt_start/2)+1,
((CASE sproc.stmt_end
WHEN -1 THEN DATALENGTH(sqltext.text)
WHEN 0 THEN DATALENGTH(sqltext.text)
ELSE sproc.stmt_end
END - sproc.stmt_start)/2) + 1),'') AS Query_SQL,
sproc.[program_name],
blockingCnt = (SELECT COUNT(*) FROM master.sys.sysprocesses blk WHERE blk.blocked <> 0 AND blk.blocked = sproc.spid)
--,sproc.*
from master.sys.sysprocesses sproc
OUTER APPLY master.sys.dm_exec_sql_text(sproc.sql_handle) AS sqltext
where sproc.spid <> @@SPID
AND sproc.spid > 50
AND sproc.ecid = 0
AND sproc.cmd <> 'AWAITING COMMAND'
AND sproc.cmd NOT IN ('DBMIRRORING_CMD','WAITFOR','BACKUP')
AND sproc.loginame <> 'sa'
AND sproc.loginame NOT IN ('domain\serviceaccount','NT AUTHORITY\SYSTEM')
AND DB_NAME(sproc.dbid) = 'databasenamehere' -- Specific database
AND datediff(mi,sproc.last_batch,getdate()) > 5 -- Running longer than x mins
ORDER BY sproc.spid, sproc.ecid
OPEN longquery
FETCH NEXT FROM longquery INTO @servername, @loginid, @ntdomain, @ntusername,
@hostname, @hostproc, @spid, @execcont, @waittype,
@waitresource, @waittime, @blockedby, @starttime, @runtimesecs,
@runtimemin, @status, @dbname, @commandtype, @sqllen, @sqlcmd, @progname, @blkcnt
WHILE @@FETCH_STATUS = 0
BEGIN
SET @killcmd = 0
/*
-- Kill specific select statement running > X mins
IF @dbname='abc'
AND (@LoginID = 'specificlogin' OR @LoginID = 'specificlogin' OR @LoginID = 'specificlogin')
AND @commandtype = 'SELECT' AND @runtimemin > 10
BEGIN
SET @killcmd = 1
END
*/
IF @killcmd = 1
BEGIN
SET @subject = 'KILLED: ' + @servername + ' SPID: ' + @spid + ' process running for ' + @runtimemin + ' Mins'
DECLARE @sql varchar(1000)
SET @sql = 'KILL ' + @spid + ';'
EXEC (@sql)
END
ELSE
BEGIN
SET @subject = CASE WHEN @blkcnt > 0 THEN 'Blocking: ' WHEN @blockedby <> 0 THEN 'Blocked: ' ELSE 'Warning: ' END + @servername + ' SPID: ' + @spid + ' Runtime: ' + @runtimemin + ' Mins'
END
SET @body = 'Server: ' + @servername + CHAR(10)
SET @body = @body + 'SPID: ' + @spid + CHAR(10)
SET @body = @body + 'Start Time: ' + CONVERT(varchar(25), @starttime, 100) + ' Running for: ' + @runtimemin + ' Mins' + CHAR(10)
SET @body = @body + 'Username: ' + @loginid + CHAR(10)
SET @body = @body + 'Database: ' + @dbname + CHAR(10)
SET @body = @body + 'Wait: ' + RTRIM(@waittype) + ' ' + @waittime + 'ms ' + CASE WHEN @blockedby <> 0 THEN 'Blocked By SPID: ' + @blockedby ELSE '' END+ CHAR(10)
SET @body = @body + 'From Host: ' + RTRIM(@hostname) + ' PID: ' + @hostproc + CHAR(10)
SET @body = @body + 'Program Name: ' + RTRIM(@progname) + CHAR(10)
SET @body = @body + CHAR(10) + 'Query: ' + LEFT(@sqlcmd,1000) + CASE WHEN LEN(@sqlcmd)>1000 THEN ' ...more...' ELSE '' END + CHAR(10)
SET @body = @body + CHAR(10) + CHAR(10) + '******* THIS IS AN UNMONITORED MAILBOX. PLEASE DO NOT REPLY TO THIS EMAIL ******* ' + CHAR(10)
--PRINT @subject
--PRINT @body
-- Send email
EXEC msdb.dbo.sp_send_dbmail @recipients = @emaildba, @subject=@subject, @body=@body, @body_format = 'TEXT'
FETCH NEXT FROM longquery INTO @servername, @loginid, @ntdomain, @ntusername,
@hostname, @hostproc, @spid, @execcont, @waittype,
@waitresource, @waittime, @blockedby, @starttime, @runtimesecs,
@runtimemin, @status, @dbname, @commandtype, @sqllen, @sqlcmd, @progname, @blkcnt
END
CLOSE longquery
DEALLOCATE longquery
SET NOCOUNT OFF
RETURN