April 3, 2019 at 5:25 pm
I have this code which checks for long running queries and emails me if the query is running for more than 5 minutes. I have set it up for 1 minutes for testing. I run this query which takes more than 1 minute to run but when I execute this stored procedure it doesn't send me email. DBMail is configured and works for pretty much all the other scripts I have in place. What am I missing?
USE Master
GO
alter procedure [dbo].[spLongRunningQueries]
as
DECLARE @LongRunningQuery TABLE
(
SPID INT,
DBName varchar(100),
HostName varchar(200),
LoginTime DATETIME,
LoginName VARCHAR(30),
ProgramName VARCHAR(200),
Running_time VARCHAR(30),
SQL_Text VARCHAR(MAX)
)
DECLARE @body1 VARCHAR(MAX)
DECLARE @subject1 VARCHAR(64)
DECLARE @css VARCHAR(MAX)
DECLARE @ProfileName varchar(200)
SELECT @ProfileName = name from msdb.dbo.sysmail_profile
DECLARE @SPID INT
SELECT @SPID = spid FROM master.sys.sysprocesses
INSERT INTO @LongRunningQuery
SELECT
SPID,
DB_NAME(SP.DBID) AS DBNAME,
HOSTNAME,LOGIN_TIME,LOGINAME,
PROGRAM_NAME,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
/* End of Article Code */
SUBSTRING(est.text, (ER.statement_start_offset/2)+1,
((CASE ER.statement_end_offset
WHEN -1 THEN DATALENGTH(est.text)
ELSE ER.statement_end_offset
END - ER.statement_start_offset)/2) + 1) AS QueryText
FROM master.sys.sysprocesses SP
INNER JOIN sys.dm_exec_requests ER
ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
WHERE start_time <= DATEADD(MINUTE, -1, GETDATE())
AND text <> 'sp_server_diagnostics'
AND DB_NAME(SP.DBID) NOT IN ('master','model','msdb','tempdb')
ORDER BY CPU DESC
SELECT * FROM @LongRunningQuery
IF EXISTS (SELECT TOP 1 SPID FROM @LongRunningQuery)
BEGIN
DECLARE report_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT SPID FROM @LongRunningQuery
OPEN report_cursor
FETCH NEXT FROM report_cursor INTO @SPID
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM report_cursor INTO @SPID
END
CLOSE report_cursor
DEALLOCATE report_cursor
SET @css = '
<style type="text/css">
#body {
font-family: verdana,arial,sans-serif;
font-size: 12px;
background: #FFF;
width: auto;
height: 525px;
margin: auto;
position: relative;
overflow: auto;
}
p {
font-family: verdana,arial,sans-serif;
padding: 5px 0px 0px;
}
.gray {
font-weight: 600;
color: #9A8B7D;
}
.DimGray {
font-weight: 600;
color: #696969;
}
.results {
font-family: verdana,arial,sans-serif;
border-collapse: collapse;
width: 100%;
margin: auto;
}
.resultsTitle {
font-family: Verdana,Arial,sans-serif;
background: #696969;
font-size: 12px;
font-weight: 600;
color: #FFF;
padding: 5px;
border-color: #FFF;
border-width: 2px;
border-style: solid;
}
th {
font-family: verdana,arial,sans-serif;
background: #9A8B7D;
font-size: 13px;
font-weight: 500;
color: #FFF;
padding: 5px;
border-color: #FFF;
border-width: 2px;
border-style: solid;
}
td {
font-family: verdana,arial,sans-serif;
background: #DDD;
font-size:12px;
padding: 5px;
border-color: #FFF;
border-width: 2px;
border-style: solid;
}
</style>'
SET @body1 = '<html><head><title>DB Mail Alert</title>' + @css + '</head>
<body>
<tr>
<th class="resultsTitle" colspan="5">List of Long Running Queries</th>
</tr>
<tr>
<th>SPID</th>
<th>DBName</th>
<th>HostName</th>
<th>LoginTime</th>
<th>LoginName</th>
<th>ProgramName</th>
<th>Running_time</th>
<th>SQL_Text</th>
</tr>'
SELECT @body1 = @body1 + '<tr>
<td>' + SPID + '</td>
<td>' + DBName + '</td>
<td>' + HostName + '</td>
<td>' + LoginTime + '</td>
<td>' + LoginName + '</td>
<td>' + ProgramName + '</td>
<td>' + Running_time + '</td>
<td>' + SQL_Text + '</td>
</tr>'
FROM @LongRunningQuery
SET @body1 = @body1 +
'</table>
</body>
</html>'
SET @subject1 = 'Long Running Queries on ' + @@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients = --EmailAddress,
@body = @body1,
@subject = @subject1,
@body_format = 'HTML'
END
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
April 3, 2019 at 5:27 pm
This was removed by the editor as SPAM
April 3, 2019 at 6:05 pm
It's working now.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
April 4, 2019 at 4:39 pm
It's working now.
So tell us how and why please...
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply