SQL Server Performance Forum – Threads Archive

Configuring Alerts in servers with limited support

I need to monitor jobs and i should be alerted if any job fails. I can not use mail or netsend option since both services are disabled. Pager option is also ruled out due to lack of facility.Kindly explain all the other possible ways to implement a better mechanism to trigger an alert.

You can use Service Broker to forward alerts for you. I do not know the technical details, but it was a topic discussed at PASS this year.

is there any particular reason for not enabling Database Mail in SQL Server 2005. Madhu

Refer to the Microsoft website for Notification services too in this regard. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

I believe you can create SSIS package and schedule it to run everty 1/5 minutes and send an email.. In SSIS:
Use SQL EXEC TASK to check the fauled jobs if there are any failed jobs…Send the email notification using MIAL TASK in SSIS. I don’t think you need to configure the sql mail to use this MAIL task in SSIS.
Mohammed U.

Thanx a lot for all those who took the strain to reply me.. I found an alternate way with which i spotted out all those jobs which failed by querying sysjobhistory table for jobs having status other than "1". P.S: Mohammed : Could you please explain a bit more bout SSIS??

quote:Originally posted by sainarayanan I need to monitor jobs and i should be alerted if any job fails. I can not use mail or netsend option since both services are disabled. Pager option is also ruled out due to lack of facility.Kindly explain all the other possible ways to implement a better mechanism to trigger an alert.

Take your time to read through Books online in this case. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

Use the following script to get the details of failed and cancelled jobs into a table in SQL EXECUTE TASK in SSIS once it is succeeded you send email using MAIL TASK in SSIS pacakge to send an email….. declare @days tinyint select @days = 1 DECLARE @job_id UNIQUEIDENTIFIER , @job_name sysname , @step_id INT , @sql_message_id INT , @sql_severity INT , @start_run_date INT , — YYYYMMDD @end_run_date INT , — YYYYMMDD @start_run_time INT , — HHMMSS @end_run_time INT , — HHMMSS @minimum_run_duration INT , — HHMMSS @run_status INT , — SQLAGENT_EXEC_X code @minimum_retries INT , @oldest_first INT , — Or 1 @server NVARCHAR(30) SELECT @job_id = NULL, @job_name = NULL, @step_id = NULL, @sql_message_id = NULL, @sql_severity = NULL, @start_run_date = NULL, — YYYYMMDD @end_run_date = NULL, — YYYYMMDD @start_run_time = NULL, — HHMMSS @end_run_time = NULL, — HHMMSS @minimum_run_duration = NULL, — HHMMSS @run_status = NULL, — SQLAGENT_EXEC_X code @minimum_retries = NULL, @oldest_first = 0, — Or 1 @server = NULL SELECT –sjh.instance_id, — This is included just for ordering purposes –sj.job_id, server = case sjh.server when ‘(local)’ then @@servername else sjh.server end, job_name = sj.name, sjh.step_id, sjh.step_name, –sjh.sql_message_id, –sjh.sql_severity, sjh.message, RunStatus = case sjh.run_status when 0 then ‘failed’ when 3 then ‘Cancelled’ end, — sjh.run_date, — sjh.run_time, –sjh.run_duration, –Convert Integer date to regular datetime SUBSTRING(CAST(sjh.run_date AS CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />),5,2) + ‘/’ + RIGHT(CAST(sjh.run_date AS CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />),2) + ‘/’ + LEFT(CAST(sjh.run_date AS CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />),4) as run_date –Change run time into something you can reecognize (hh:mm<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />s) , LEFT(RIGHT(‘000000’ + CAST(run_time AS VARCHAR(10)),6),2) + ‘:’ + SUBSTRING(RIGHT(‘000000’ + CAST(run_time AS VARCHAR(10)),6),3,2) + ‘:’ + RIGHT(RIGHT(‘000000′ + CAST(run_time AS VARCHAR(10)),6),2) as run_time –Change run duration into something you caan recognize (hh:mm<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />s) , LEFT(RIGHT(‘000000’ + CAST(run_duration AS VARCHAR(10)),6),2) + ‘:’ + SUBSTRING(RIGHT(‘000000’ + CAST(run_duration AS VARCHAR(10)),6),3,2) + ‘:’ + RIGHT(RIGHT(‘000000′ + CAST(run_duration AS VARCHAR(10)),6),2) run_duration, operator_emailed = so1.name, operator_netsent = so2.name, operator_paged = so3.name –sjh.retries_attempted FROM msdb.dbo.sysjobhistory sjh LEFT OUTER JOIN msdb.dbo.sysoperators so1 ON (sjh.operator_id_emailed = so1.id) LEFT OUTER JOIN msdb.dbo.sysoperators so2 ON (sjh.operator_id_netsent = so2.id) LEFT OUTER JOIN msdb.dbo.sysoperators so3 ON (sjh.operator_id_paged = so3.id), msdb.dbo.sysjobs_view sj WHERE (sj.job_id = sjh.job_id) AND ((@job_id IS NULL) OR (@job_id = sjh.job_id)) AND ((@step_id IS NULL) OR (@step_id = sjh.step_id)) AND ((@sql_message_id IS NULL) OR (@sql_message_id = sjh.sql_message_id)) AND ((@sql_severity IS NULL) OR (@sql_severity = sjh.sql_severity)) AND ((@start_run_date IS NULL) OR (sjh.run_date >= @start_run_date)) AND ((@end_run_date IS NULL) OR (sjh.run_date <= @end_run_date)) AND ((@start_run_time IS NULL) OR (sjh.run_time >= @start_run_time)) AND ((@end_run_time IS NULL) OR (sjh.run_time <= @end_run_time)) AND ((@minimum_run_duration IS NULL) OR (sjh.run_duration >= @minimum_run_duration)) AND ((@run_status IS NULL) OR (@run_status = sjh.run_status)) AND ((@minimum_retries IS NULL) OR (sjh.retries_attempted >= @minimum_retries)) AND ((@server IS NULL) OR (sjh.server = @server)) AND sjh.run_status in (0,3) AND sjh.step_id <>0 AND SUBSTRING(CAST(sjh.run_date AS CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />),5,2) + ‘/’ + RIGHT(CAST(sjh.run_date AS CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />),2) + ‘/’ + LEFT(CAST(sjh.run_date AS CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />),4) > getdate() – @days ORDER BY sjh.server –(sjh.instance_id * @order_by) Mohammed U.

]]>