InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 07:51:41 GMT20http://www.sqlservercentral.com/Forums/Topic1394676-392-1.aspxThanks for the reply. Its working fine.Tue, 11 Dec 2012 03:06:10 GMTrajkiran.panchagirihttp://www.sqlservercentral.com/Forums/Topic1394676-392-1.aspxThis isn't pretty but it works well - I just threw it into a Job running hourly[code="sql"]CREATE PROCEDURE [dbo].[utl_dba_CheckSQLJobsForErrors] @notifyEmailGroup varchar(300) = 'dba_alerts@company.com'AS /* USED TO MONITOR MSDB JOB TABLES AND SEND AN EMAIL ALERT WHEN AN ERROR HAS OCCURED ON A JOB STEP Looks 20 Minutes back for Job Errors - This makes sure that it will send multiple emails */DECLARE @sqlSelect nvarchar(500)CREATE TABLE #SQLAGENTERRORS ( [id] int IDENTITY(1, 1) NOT NULL, [SQL AGENT JOB NAME] [sysname] NOT NULL, [step_id] [int] NOT NULL, [step_name] [sysname] NOT NULL, [sql_message_id] [int] NOT NULL, [sql_severity] [int] NOT NULL, [message] [nvarchar](1024) NULL, [run_status] [int] NOT NULL, [run_date] [int] NOT NULL, [run_time] [int] NOT NULL, [run_duration] [int] NOT NULL, [retries_attempted] [int] NOT NULL, [server] [sysname] NOT NULL) SET @sqlSelect = 'SELECT DISTINCT a.[Name][SQL AGENT JOB NAME],[step_id],[step_name],0[sql_message_id]' + ',0[sql_severity],0[message],0[run_status],[run_date],[run_time],0[run_duration] ' + ', 0 [retries_attempted],[server] FROM [msdb].[dbo].[sysjobs] as a JOIN [msdb].[dbo].[sysjobhistory] as b on a.job_id=b.job_id ' + 'WHERE b.Sql_Severity <> 0 AND run_date = REPLACE(CONVERT(VARCHAR(10),GETDATE(),121),''-'','''') ' + 'AND [run_time] >= ''' + REPLACE(CONVERT(varchar(20), DATEADD(minute, -20, GETDATE()), 08), ':', '') + '''' + ' ORDER BY [run_date],a.[Name],b.[step_id] '-- WRITE TEMP TABLE TO FORMAT DATA INSERT INTO #SQLAGENTERRORS EXECUTE MASTER..SP_EXECUTESQL @sqlSelectIF (SELECT COUNT(1) FROM #SQLAGENTERRORS) > 0 BEGIN DECLARE @calldate datetime, @sqlStatement nvarchar(350), @subject varchar(128), @body varchar(4000) SET @body = '' SET @subject = 'Job Step Failures Detected ' + CONVERT(varchar(25), GETDATE(), 120) IF (SELECT COUNT(1) FROM #SQLAGENTERRORS) > 0 BEGIN DECLARE @Run_Date varchar(12), @Run_Time varchar(12), @SQLAGENTJOBNAME varchar(200), @step_id varchar(12), @step_name varchar(200), @sql_message_id varchar(12), @sql_severity varchar(12), @message varchar(500), @run_status varchar(12), @run_duration varchar(12), @retries_attempted varchar(12), @server varchar(25), @id int, @maxID int SELECT @id = 1, @maxID = MAX(ID) FROM #SQLAGENTERRORS SELECT * FROM #SQLAGENTERRORS WHILE @id <= @maxID BEGIN SELECT @Run_Date = RTRIM(SUBSTRING(CONVERT(varchar(8), [run_date]), 1, 4) + '-' + SUBSTRING(CONVERT(varchar(8), [run_date]), 5, 2) + '-' + SUBSTRING(CONVERT(varchar(8), [run_date]), 7, 2)), @Run_Time = CASE WHEN LEN([run_time]) = 5 THEN RTRIM('0' + SUBSTRING(CONVERT(varchar(10), [run_time]), 1, 1) + ':' + SUBSTRING(CONVERT(varchar(10), [run_time]), 2, 2) + ':' + SUBSTRING(CONVERT(varchar(10), [run_time]), 4, 2)) ELSE RTRIM(SUBSTRING(CONVERT(varchar(10), [run_time]), 1, 2) + ':' + SUBSTRING(CONVERT(varchar(10), [run_time]), 3, 2) + ':' + SUBSTRING(CONVERT(varchar(10), [run_time]), 5, 2)) END, @SQLAGENTJOBNAME = RTRIM([SQL AGENT JOB NAME]), @step_id = RTRIM([step_id]), @step_name = RTRIM([step_name]), @sql_message_id = RTRIM([sql_message_id]), @sql_severity = RTRIM([sql_severity]), @message = RTRIM([message]), @run_status = RTRIM([run_status]), @run_duration = RTRIM([run_duration]), @retries_attempted = RTRIM([retries_attempted]), @server = RTRIM([server]) FROM #SQLAGENTERRORS WHERE id = @id SELECT @body = @body + 'ERROR #' + RTRIM(CONVERT(varchar(10), @id)) + ' - ' + @SQLAGENTJOBNAME + ' on ' + @server + 'RUN DATE: ' + @Run_Date + ' Run Time: ' + @Run_Time + '=======================================Step: ' + @step_id + ' - ' + @step_name + '' SET @id = @id + 1 END EXEC msdb..sp_send_dbmail @recipients = @notifyEmailGroup, @subject = @subject, @body = @body END ENDDROP TABLE #SQLAGENTERRORS[/code]Mon, 10 Dec 2012 16:09:12 GMTMyDoggieJessiehttp://www.sqlservercentral.com/Forums/Topic1394676-392-1.aspxThe reason is to just capture the reason why its failed and trigger a mail for every hour. The reason for truncating the temp table is to avoid the increase in size of the table or database.Mon, 10 Dec 2012 10:14:06 GMTrajkiran.panchagirihttp://www.sqlservercentral.com/Forums/Topic1394676-392-1.aspxAny particular reason to log it to a table, instead of just definining a view that queries the last hour of data?Either way, you'll get most of your data out of msdb.dbo.sysjobhistory (details: [url]http://msdn.microsoft.com/en-us/library/aa260416(v=SQL.80).aspx[/url]) and possibly out of the error log (there are ways to query this, but so far as I know they're all undocumented, so you'll need to research them yourself). Most of the data should be in the sysjobhistory table.Mon, 10 Dec 2012 09:49:01 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1394676-392-1.aspxHi Friends, I need a procedure which should capture the job failure. This SP should capture the below elements:1. Server Name2. Job Name3. Cause of job failure The SP provided will be executed for hourly once. It should capture the data into a table every 1 hr. The very next time it runs the temp table must be truncated.Thanks in Advance.Mon, 10 Dec 2012 09:25:49 GMTrajkiran.panchagiri