ALTER proc [dbo].[usp_EmailLongRunningSQL]
as
begin
declare@LongRunningQueries AS TABLE
(
  lrqId int IDENTITY(1,1) PRIMARY KEY,
  spid int NULL,
  batch_duration bigintNULL,
  program_namenvarchar(500) NULL,
  hostname nvarchar(100) NULL,
  loginame nvarchar(100) NULL,
  sqltext nvarchar(max) NULL
)
-- variabledeclaratuions
DECLARE @exectime DATETIME
DECLARE @tableHTMLNVARCHAR(MAX)
DECLARE @Handle VARBINARY (85)
DECLARE @SPID INT
DECLARE @sqltext NVARCHAR(MAX)
DECLARE @timeLimitsmallint
declare @Sub as varchar(100)
set @Sub = @@servername + 'Long Running  Query found'
SET @timeLimit=(3*60) -- minutes
 
-- WAITFOR DELAY'00:01:05' -- uncomment for testing (1min:5sec)
 
-- populate thetable with execution info, you don't have to use top 1
INSERT INTO @LongRunningQueries(spid, batch_duration, program_name, hostname, loginame)
 
SELECT top 5
    P.spid
  , convert(bigint,DateDiff(ms,P.last_batch,getdate()))/1000
  , P.program_name
  , P.hostname
  , P.loginame
FROM master.dbo.sysprocesses P WITH(NOLOCK)
WHERE (P.spid > 50)
AND P.status NOT IN('background', 'sleeping')
AND P.cmd NOT IN('AWAITING COMMAND','MIRROR HANDLER','LAZY WRITER','CHECKPOINT SLEEP','RA MANAGER')
AND convert(bigint,DateDiff(ms,P.last_batch,getdate()))/1000 >@timeLImit
 
 
-- use a cursorto update the sqltext for each pid
DECLARE @lrqId int
DECLARE mycur cursor for
  SELECT lrqId from @LongRunningQueries
  ORDER BY lrqId
 
OPEN mycur
FETCH NEXT FROM mycur INTO @lrqId
 
WHILE @@FETCH_STATUS=0
BEGIN
 
  SET @SPID =(SELECT spid from @LongRunningQueries WHERElrqId=@lrqId)
 
  -- get the SQL theSPID is executing
  SELECT@Handle = sql_handleFROM master.dbo.sysprocesses WHEREspid = @SPID
 
  UPDATE@LongRunningQueries
  SET sqltext =(SELECT text FROM sys.dm_exec_sql_text(@Handle))
  WHERE lrqId = @lrqId
 
  FETCH NEXT FROM mycur INTO @lrqId
END
CLOSE mycur
DEALLOCATE mycur
 
DELETE FROM @LongRunningQueries WHEREsqltext IS NULLOR sqltext='' OR  program_nameLIKE '%DatabaseMail%'
 
IF EXISTS(SELECT * FROM@LongRunningQueries WHERE sqltext IS NOT NULL OR sqltext<>'')
BEGIN
-- populate atable with it's info and mail it
SET @tableHTML=
  N'
LongRunning  Querys
' +
  N'' +
  N'