Central Management Server

This is the newest thing that our team is integrating and so far I’m digging it. It’s nice to have all the servers that a DBA is in charge of held in one general location. I know that there are several good reads off hand on the web that Brent Ozar has done and I believe Kevin Kline has done over at SQLServerPedia. With this great tool also comes great responsibility; say for example you have 80 servers on your CMS system; once you log in to the Central Management Server via Management Studio you will be connected to all 80 servers. In saying that I would strongly suggest that you are very careful on what you are running. I do like the tool though; it is very  handy and I highly recommend it so ~ Check It Out

Notifications on Jobs

Have you ever been in a situation where you started to create this masterful script only to find that it was already out and available to begin with. I think that is one of the biggest advantages within the SQL community; everyone really seems to try to help each other out by publishing items, sharing knowledge on issues and solutions, pointing others in the right direction. In saying that I recently wanted to find every job on every server that did not have a notification; simple enough task. As I sat down and began to write the code out I began to read through some blogs and I came across one that was already doing the same thing that I had just started on. Jonathan (a.k.a. FatherJack) wrote an article based on finding notification settings on SQL jobs. After review I ended up tweaking the script somewhat to only pull jobs I had enabled at the time and also to only order by the actual email alert level. Below is the tweaked script (does not include the only enabled filter):

SELECT  [j].[name], 
        CASE WHEN[j].[notify_level_eventlog]=1THEN'On success' 
            WHEN[j].[notify_level_eventlog]=2THEN'On failure' 
            WHEN[j].[notify_level_eventlog]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Event Log Entry], 
        CASE WHEN[j].[notify_level_page]=1THEN'On success' 
            WHEN[j].[notify_level_page]=2THEN'On failure' 
            WHEN[j].[notify_level_page]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Page Alert], 
        CASE WHEN[j].[notify_level_netsend]=1THEN'On success' 
            WHEN[j].[notify_level_netsend]=2THEN'On failure' 
            WHEN[j].[notify_level_netsend]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Net Send Alert], 
        CASE WHEN[j].[notify_level_email]=1THEN'On success' 
            WHEN[j].[notify_level_email]=2THEN'On failure' 
            WHEN[j].[notify_level_email]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Email Alert] 
FROM    [dbo].[sysjobs] AS j 
ORDER BY [j].[notify_level_email] DESC

I give props to Jonathan and to view his script along with an additional script that shows operators you can click here