Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

How to find SQL Agent Jobs without Notification Operator Configured?

We can either use SQL Server Management Studio or use Transact-SQL query to find any SQL Agent jobs that have been setup without notification operator.

The following are the steps to find SQL Agent jobs without notification operator via SQL Server Management Studio:

  1. In Object Explorer, expand SQL Agent, then expand Jobs folder (see below):

  2. Next, right-click any SQL Agent Job and choose Properties and then click Notifications (see below):



  3. This is a good idea only if we have a small number of SQL Agent Jobs on SQL Server. However, most production systems literally contain hundreds of SQL Agent Jobs, and it is quite difficult to find this information like that. In this situation, you might use the following Transact-SQL query, which I’ve written a while ago to find SQL Agent Jobs without notification operator:

    USE [msdb]
    GO
    
    SET NOCOUNT ON;
    
    SELECT 'SQL Agent job(s) without notification operator found:' AS [Message]
    
    SELECT j.[name] AS [JobName]
    FROM [dbo].[sysjobs] j
    LEFT JOIN [dbo].[sysoperators] o
    ON (j.[notify_email_operator_id] = o.[id])
    WHERE j.[enabled] = 1
    AND j.[notify_level_email] NOT IN (1, 2, 3)
    GO
    

    I hope you will find this information useful ;)


Comments

Leave a comment on the original post [basitaalishan.com, opens in a new window]

Loading comments...