[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

sql agent alerts

On my servers I wanted to configure the sql alerts. I used the script provided by BrentOzar from this website: http://www.brentozar.com/blitz/configure-sql-server-alerts/
The problem is that parameter @delay_between_responses=60 is set to 60 seconds, and I don't want to be flooded with emails every minute. Now I want to change this settings to 2hours.
Is there any automatic way to do this. I have a lot of sql servers and I don't want to do this mannualy alert by alert
0
dedri
Asked:
dedri
1 Solution
 
knightEknightCommented:
Can't you just change the value from 60 to 7200 in the script?

  @delay_between_responses=7200
0
 
dedriAuthor Commented:
executing the script will generate an error message because the alerts have been already created on servers.
0
 
Jim P.Commented:
What is the problem with the answers? Did they not work?

What you should probably do is create a nightly scheduled task that runs every night and can read/write to a central point. Then you don't have to touch the servers manually the next time. Just put a new command in the batch file.
0
 
David ToddSenior DBACommented:
Hi,

Looking up sp_add_alert gives
http://technet.microsoft.com/en-us/library/ms189531.aspx

scroll down to the bottom and find sp_update_alert posted there
http://technet.microsoft.com/en-us/library/ms178616.aspx

I think that if you do a search and replace on Brent's script, and change sp_add_alert to sp_update_alert, and change the response delay value, then you'll be okay.

I think a value in the 10 minute range will be good. That is, by the time you or a colleague is paged, and you find your computer and connect in will take around 10 minutes. If a particular alert on a particular server goes off with ponderous regularity you have two or three options - create a rule on the alert mailbox and put those emails in a sub-folder; increase the delay on just that alert on that server; disable the alert on that server (bad idea)

HTH
  David
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now