Failed Job Notification To Event Log


Earlier, I had written a post about a failed job issue. A friend of mine, after reading this post, asked how should he verify the SQL jobs running in his environment without this notification option? By executing the following script, we can get the list of sql jobs, which are currently enabled, along with their Notification Status.

-- Enabled Jobs along with Notification Status
use [msdb];
go
select	sj.[name] as [Job_Name],
	sj.[notify_level_eventlog] as [Write_to_Application_Eventlog]
from dbo.sysjobs as sj
where sj.[enabled] = 1;
go

To list only the jobs, where the job status will not be written to the Windows Application event log, modify the above script as shown below:

-- List of jobs,upon failure, will not be written to the Windows Application Event Log
use [msdb];
go
select	sj.[name] as [Job_Name],
	sj.[notify_level_eventlog] as [Write_to_Application_Eventlog]
from dbo.sysjobs as sj
where (sj.[enabled] = 1) and (sj.[notify_level_eventlog] = 0);
go

If you have registered a list of SQL Server instances under the Registered Server option in SSMS, you can execute the above query to list all the jobs where the job failure event will not be written to Windows Application event log.

Job_Event_Notification

To read more about the various columns from sysjobs, read the following post from Books Online.

Happy Learning Smile

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Automation in SQL Server, How To, Jobs, Just Learned, SQL Server, T-SQL and tagged , , , , , . Bookmark the permalink.

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