Solved

sql agent alerts

Posted on 2014-03-12
5
358 Views
Last Modified: 2014-03-17
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
Comment
Question by:dedri
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 39923805
Can't you just change the value from 60 to 7200 in the script?

  @delay_between_responses=7200
0
 

Author Comment

by:dedri
ID: 39924670
executing the script will generate an error message because the alerts have been already created on servers.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39933458
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
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 39933559
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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question