Excellent article. I have tweaked this to run on MSSQL 2005.

• TIME is not a valid data-type in 2005
• fn_get_sql is depreciated and replaced with sys.dm_exec_sql_text
• I have also adjusted it to use a cursor to allow more than than just "top 1" (using top 5 as an example)
• I also opted to use a temp table to store running data

- LarrySql 😉

DELCARE @LongRunningQueries AS TABLE

(

lrqId int IDENTITY(1,1) PRIMARY KEY,

spid int NULL,

batch_duration bigint NULL,

program_name nvarchar(500) NULL,

hostname nvarchar(100) NULL,

loginame nvarchar(100) NULL,

sqltext nvarchar(max) NULL

)

-- variable declaratuions

DECLARE @exectime DATETIME

DECLARE @tableHTML NVARCHAR(MAX)

DECLARE @Handle VARBINARY (85)

DECLARE @SPID INT

DECLARE @sqltext NVARCHAR(MAX)

DECLARE @timeLimit smallint

SET @timeLimit = (1*60) -- minutes

-- WAITFOR DELAY '00:01:05' -- uncomment for testing (1min:5sec)

-- populate the table 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 cursor to 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 WHERE lrqId=@lrqId)

-- get the SQL the SPID is executing

SELECT @Handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = @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

WHERE sqltext IS NULL OR sqltext=''

-- populate a table with it's info and mail it

SET @tableHTML =

N'<H1>Long Running WFM Querys</H1>' +

N'<table border="1">' +

N'<tr><th>SPID</th>' +

N'<th>Duration</th>' +

N'<th>Application</th>' +

N'<th>HostName</th>' +

N'<th>Login</th>' +

N'<th>SQL Executing</th></tr>' +

CAST ( ( SELECT td = T.spid, '',

td = T.batch_duration, '',

td = T.[program_name], '',

td = T.hostname, '',

td = T.loginame, '',

td = T.sqltext, ''

FROM

@LongRunningQueries T

FOR XML PATH('tr'), TYPE

) AS NVARCHAR(MAX) ) +

N'</table>'

-- if @tableHTML is NULL, mail will not get sent

EXEC msdb.dbo.sp_send_dbmail

-- @profile_name = 'DEFAULT',

@recipients= 'you@yourdomain.com',

@subject = 'Long Running WFM Query found',

@body = @tableHTML,

@body_format = 'HTML';