Stairway to SQL Server Agent - Step 3: Agent Alerts and Operators

  • Comments posted to this topic are about the item Stairway to SQL Server Agent - Step 3: Agent Alerts and Operators

  • Screenshot 9 is the same as Screenshot 8 🙂

  • Indeed, looks like an editing error. I'll email the editors right away and let them know.... In the meantime I attached the correct screenshot here.

  • Interesting part on the alerts that don't fire, worth the read right there.

  • The article should be fixed now..

    Thanks for reporting it!

    ~Kalen

  • If you can't add a distribution list to your email system, you can add multiple email addresses in the Email Name field for an Operator - just separate them with a semi-colon. That's actually how we have our Admins operator set up.

    Thanks,

    MWise

  • Nice one....

  • Forgive me if this has already been addressed, but if I setup an alert like the article shows for severity level 19, will it also alert for anything above that severity level or do I have to setup individual alerts?

    I created new topics with this question in the wrong area and can't figure out how to delete those. Sorry.

  • One for each severity level. Here's how to test:

    --create an alert, using severity 9

    EXEC msdb.dbo.sp_add_alert @name=N'RWTest',

    @message_id=0,

    @severity=9,

    @enabled=1,

    @delay_between_responses=0,

    @include_event_description_in=0,

    @database_name=N'master',

    @category_name=N'[Uncategorized]',

    @job_id=N'00000000-0000-0000-0000-000000000000'

    GO

    -- now add a message that will have severity 10 by default

    use master;

    exec sp_addmessage 50001,10,N'Test Message 50001';

    -- fire an event to the windows event log that will have severity 10

    exec xp_logevent 50001,'rw test 1',INFORMATIONAL

    --now go check the alert -> History tab, and you will see Number of occurrences has NOT incremented

    --add an alert to use a severity 10 fire now:

    EXEC msdb.dbo.sp_add_alert @name=N'RWTest2',

    @message_id=0,

    @severity=10,

    @enabled=1,

    @delay_between_responses=0,

    @include_event_description_in=0,

    @database_name=N'master',

    @category_name=N'[Uncategorized]',

    @job_id=N'00000000-0000-0000-0000-000000000000'

    -- fire an event to the windows event log that will have severity 10

    exec xp_logevent 50001,'rw test 1',INFORMATIONAL

    --now go check the alert -> History tab, and you will see Number of occurrences has incremented

    Hope this helps

    -Richard

  • Great information Richard!

    Is there anyway to "automate" setting up multiple alerts ranging in severity from 19-25?

  • Goodness that is the way to automate!

    Can that query also setup responses to Notify operators via email and Pager and include alert error text via Email and Pager?

  • Yes. The easiest way is to go set something up like you want it in SSMS, then script out the alert.

    And in terms of further automation, your best bet beyond what I wrote would be to investigate scripting the whole thing in powershell.

  • That is even more good information... I actually ordered a book on Powershell and it should be in later this week. Thank you!!

  • Note

    A common question about operators is how you can send alerts to multiple people. The way you do this is to use mail distribution lists through your email system, as SQL Server Agent will only send alerts to a single operator. You would therefore define the operator in SQL Server Agent as a distribution list, and everyone on the list will then receive the associated alerts.

    Or...you can create an operator consisting of multiple individuals. Just separate the email names with a semicolon. A MDL may be preferable but in some cases may not be an option.

    (in SQL2005 anyway)


    Cursors are useful if you don't know SQL

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply