April 17, 2012 at 12:40 pm
Hello,
I have a job(job1) scheduled for every 10 min. i want to setup a new monitoring job ever 1 hour which will notify of any failure of job1 to a work group and if success just one mail should go at the end of day instead of hourly notification. Any help with scripting is appreaciated
April 17, 2012 at 12:52 pm
What have you tried so far? You can start by looking at dbo.sysjobhistory table in msdb database
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 17, 2012 at 12:57 pm
select * from (
select j.name
,Run_Date=convert(datetime,convert(varchar(8),jh.run_date))
,duration_in_Secs=jh.run_duration/10000*3600
+jh.run_duration%10000/100*60
+jh.run_duration%100
,jh.run_status
from msdb.dbo.sysjobhistory jh
join msdb.dbo.sysjobs j on jh.job_id = j.job_id
and j.name = 'storedprocedures') as J
where Run_Date between GETDATE()-1 and GETDATE()
---- i need help here. am very poor at scripting. how do i use if to to the result set from above select statement
If msdb.dbo.sysjobhistory.run_status in( 0,2,3)
EXEC master.dbo.xp_sendmail
@recipients='xxxxx@gmailcom'
,@message=' All hourly jobs successfull on getdate()'
April 17, 2012 at 1:11 pm
It sounded like a homework project, so I wanted to make sure you had at least tried something instead of just posting it here and looking for answers. When the hourly job runs, does it just need to report any errors within the last hour or all errors for the day?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 17, 2012 at 6:14 pm
Yes It has to send mail notification if any failure occurs.
April 18, 2012 at 6:19 am
Does this work for you?
declare @runStatus int,
@JobName nvarchar(300) = 'storedprocedures',
@Recipients nvarchar(300) = 'xxxxx@gmailcom',
@Message nvarchar(300) = ' All hourly jobs successfull on getdate()'
select @runStatus = run_status from (
select j.name
,Run_Date=convert(datetime,convert(varchar(8),jh.run_date))
,duration_in_Secs=jh.run_duration/10000*3600
+jh.run_duration%10000/100*60
+jh.run_duration%100
,jh.run_status
from msdb.dbo.sysjobhistory jh
inner join msdb.dbo.sysjobs j
on jh.job_id = j.job_id
and j.name = @JobName) as J
where Run_Date between GETDATE()-1 and GETDATE()
if @runStatus in( 0,2,3)
EXEC master.dbo.xp_sendmail
@recipients= @Recipients
,@message= @Message
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 18, 2012 at 8:16 am
Hey Thank you so much. But I worked on it and came up with a new one.
/* Stored procedure to hourly monitor stored procedures with Script_id 006, 005,004,006, 001 */
CREATE PROCEDURE SP_xxx_SP
AS
SELECT
DATEPART(HH,Act_time) as Hour,
script_id,
COUNT(action) as Count_No
INTO #temp_log
FROM XX.XX
WHERE act_time> DATEADD(hh,-1,getdate())
and script_id in (006,005, 004, 001)
and action='SCRIPT END'
GROUP BY DATEPART(HH,act_time),script_id
SELECT script_id into #Temp_Results from #temp_log where Count_No < 6
DECLARE @Count_UM INT
SELECT @Count_UM=COUNT(*) From #Temp_Results
IF @Count_UM>0
BEGIN
-- Email in HTML format
DECLARE @tableHTML NVARCHAR(max) ;
SELECT @SubjectText = 'Job failed'
SET @tableHTML =
N'<html><body><h1>***The following stored procedure jobs failed***</h1>' +
N'<table border="1" width="100%">' +
N'<tr bgcolor="gray"><td>script_id</td><td>MP2_POCloseDate</td>'+
CAST((
SELECT
td = script_id, ''
FROM #Temp_Results
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) + N'</table></body></html>'
/* send message on failure */
EXEC msdb.dbo.sp_send_dbmail
@Profile_Name = '******',
@recipients = '********',
@subject = @SubjectText,
@body = @tableHTML,
@body_format = 'HTML'
END
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply