| Author |
Topic |
|
cjvjr
Starting Member
7 Posts |
 Posted - 2012-05-27 : 06:33:46
|
| Hi,I have got a big problem.On out SQL instance we are using an alert, that sends a notification to an e-mailaddress when the database is full (eror 1105)For a long while this went good, we were receiving mails about a database that was full and needed cleanup or expansion.Now recently we had a situation where an application that is constantly sending transactions to the SQL instance.Every time the alert went off and a mail message was sent. By the time we expanded the database the alert had caused 7000+ mail messages (in only a period of 10 minutes).The mailboxes got stressed out by this situation.Any solutions to this? Are there other ways to get this information?greetings,Kick |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-27 : 11:38:46
|
| how have you set email alert? is it through sql agent job? if yes, you can configure it to sent one mail for every x notifications.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
cjvjr
Starting Member
7 Posts |
Posted - 2012-05-27 : 15:02:37
|
| Hi,We just created an SQL Alert (under SQL Agent).Is the "delay between responses" the setting you referring to?greetings,Kick |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-27 : 15:20:09
|
quote:
Originally posted by cjvjr
Hi,We just created an SQL Alert (under SQL Agent).Is the "delay between responses" the setting you referring to?greetings,Kick
nope.You can set custom logic inside job to count the transactions and sent alert only for every x alerts------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
cjvjr
Starting Member
7 Posts |
Posted - 2012-05-27 : 16:14:47
|
| Thanks again for the info.We are just using a SQL Alert that is triggered when error 1105 takes place. It then notifies the Operator by mail.I just discovered the "Delay between responses" option.I think is does the same, but instead of number of alerts it uses a time period.So if the delay period is long enough to solve the problem, the number of mails will also be limited.Do you agree? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-27 : 21:47:18
|
quote:
Originally posted by cjvjr
Thanks again for the info.We are just using a SQL Alert that is triggered when error 1105 takes place. It then notifies the Operator by mail.I just discovered the "Delay between responses" option.I think is does the same, but instead of number of alerts it uses a time period.So if the delay period is long enough to solve the problem, the number of mails will also be limited.Do you agree?
but how can you be sure on delay? are you sure timeperiod will be constant throughout?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
SQL Basic
Starting Member
8 Posts |
Posted - 2012-05-28 : 02:43:45
|
| One simple solution is deleted unused file or data. |
|
|
|
cjvjr
Starting Member
7 Posts |
Posted - 2012-05-28 : 05:55:20
|
quote:
Originally posted by visakh16
quote:
Originally posted by cjvjr
Thanks again for the info.We are just using a SQL Alert that is triggered when error 1105 takes place. It then notifies the Operator by mail.I just discovered the "Delay between responses" option.I think is does the same, but instead of number of alerts it uses a time period.So if the delay period is long enough to solve the problem, the number of mails will also be limited.Do you agree?
but how can you be sure on delay? are you sure timeperiod will be constant throughout?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
I don't think i quite understand your response.In ,my opinion, the delay option only solves the issue of getting lots of mails.In our case the SQL Server had sent over 30.000 mails all about the same event. The datafile got full, the application was retying to write to the database alsmost every ms and SQL was generating a alert for each of them.If i understand it correctly the delay option, the alert will go off and sent a response. It then waits for the timeout period to expire before sending another response.In the meantime our operators are already notified about the incident, so they will take action. If the period is going to be longer than the delay period and the operator hasn't been able to solve the problem, the alert will start mailing again.With the delay option we will be eliminating the mailbomb to the Exchange servers. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-28 : 13:52:39
|
| ok...thats kind of workaroundWhat i suggested was to log this onto table rather than sending alerts then and after every say x alert make it send a single notification to ops------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
too many mails when database is full