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.
To read more about the various columns from sysjobs, read the following post from Books Online.