Easy script to notify failed jobs [SQL Server]

The script provided below is a easy way to add notification for all failed jobs.

Requirements:
1- DatabaseMail Enabled
2- Enable DatabaseMail on the SQL Server Agent ; properties ; Alert System

USE [msdb]
GO
--create a N'DBA_Operator'
EXEC msdb.dbo.sp_add_operator @name=N'DBA_Operator',          
@enabled=1,
@email_address=N'DBAs@yourdomain.com'
GO
--add notifications for failure to all jobs
DECLARE @QuotedIdentifier char(1); SET @QuotedIdentifier = '' -- use '''' for single quote
DECLARE @ListDelimeter char(1); SET @ListDelimeter = ';'
DECLARE @CSVlist varchar(max) --use varchar(8000) for SQL Server 2000

--no event log, email on failure
SELECT     @CSVlist = COALESCE(@CSVlist + @ListDelimeter, '') + @QuotedIdentifier +
'
EXEC msdb.dbo.sp_update_job @job_id=N'''
+ convert(varchar(max),[job_id]) +
''',
          @notify_level_eventlog=0,
          @notify_level_email=2,
          @notify_email_operator_name=N''DBA_Operator'''
+ @QuotedIdentifier
from msdb.dbo.sysjobs
Advertisements

Danilo Braga is a Database Administrator with over nine years of experience. He is a Microsoft Certified IT Professional (MCITP) Database Administrator and Developer. His experience includes solutions for management, assessment and implementation of SQL Server environments making use of best practices, performance tuning, and high availability.

Tagged with: , , ,
Posted in Administration, Alerts, Scripts

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: