July 7, 2011 at 12:14 am
Comments posted to this topic are about the item Email Long Running SPID Info
July 7, 2011 at 8:26 am
Excellent article. I have tweaked this to run on MSSQL 2005.
- 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';
July 8, 2011 at 10:02 am
Thanks Larry, and nice work on the tweaks!
I was actually asked yesterday if I could do more than just the first most expensive query, so you have saved me a bit of work 😀
July 8, 2011 at 11:30 am
Hey, my pleasure, happy to help out.
I tweaked this solution just a little further, noticing on my server that DB Mail tends to get caught as a long running process. You could also add 'suspended' to the status exception list if you are sure your processes don't go to sleep. Some logic can be added at the end to check for a count of lines where sqltext is not null, then proceed to mail out the details:
SELECT @cnt = count(*)
FROM @LongRunningQueries
WHERE sqltext IS NOT NULL and sqltext <> ''
IF @cnt > 1
BEGIN
-- set subject to include server name
SET @mySubject = @@SERVERNAME + ': Long Running Query Found'
-- if @tableHTML is NULL, mail will not get sent
EXEC msdb.dbo.sp_send_dbmail
--@profile_name = 'DEFAULT',
@recipients= 'you@yourdomain.com',
@subject = @mySubject,
@body = @tableHTML,
@body_format = 'HTML';
END
July 27, 2011 at 12:52 pm
I will recommend best practice not put your own temp table into master database, instead, create an AdminDB, put everything DBA uses into AdminDB. Include AdminDB in regular backup.
Jason
http://dbace.us
😛
May 12, 2016 at 7:27 am
Thanks for the script.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply