SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Email Long Running SPID Info


Email Long Running SPID Info

Author
Message
jamin_za
jamin_za
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 333
Comments posted to this topic are about the item Email Long Running SPID Info
LarrySql
LarrySql
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 116
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';




jamin_za
jamin_za
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 333
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 :-D
LarrySql
LarrySql
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 116
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



jswong05
jswong05
Say Hey Kid
Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)Say Hey Kid (667 reputation)

Group: General Forum Members
Points: 667 Visits: 476
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
:-P
Iwas Bornready
Iwas Bornready
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21838 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search