"The SQL Server Agent reads the application log and compares any events it finds there to alerts that you may have defined. When SQL Server Agent finds a match, it fires an alert"

This sounds totally wird to me
I would say it is wrong otherwise how can you explain that you can raise for events that never go the error log or the event log?

This is the way that SQL Server Event Alerts work, according to BOL and MSDN.

Performance alerts, and WMI alerts work differently, of course. We were referring to SQL Server Event Alerts in the passage that you quote.

Perhaps the alerts you are using are SQL Server Performance Condition Alerts?

I agree that Alerts can be very confusing. Robyn and I were continually having to try things out to check, so the article took ages to write!

 --I'm puzzled by 'Anonymous's comment
--When we wrote the article we checked using
--code like this

USE [msdb]
GO
--put in a test alert. Make sure there isn't another
EXEC msdb.dbo.sp_add_alert @name=N'Error 16 alert',
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@database_name=N'MyDatabase',
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
--change MyDatabase to the name of your database



RAISERROR ('This is NOT logged',16,1)
RAISERROR ('This is logged',16,1) WITH LOG

--now look at the History tab of the alert in SSMS
--an see how many times the alert fired.
--(number of occurrences) It will be 1

The raiserror with severity 16 is not a logged event unless you use WITH LOG. Severirty 19-25 are logged - see BOL sp_add_alert. You can't change the 'write_to_log' for system messages - see BOL sp_altermessage. The sys.messages table has an is_event_logged value that indicates if the error is logged. I think message_id 50000 can't be changed to be logging.

Error 1205 is not logged and can't be changed in SQL server 2005. It could be changed in SQL Server 2000. 1205 alerts no longer fire in SQL Server 2005. However, a WMI alert for a deadlock graph will fire. That's one reason I'm looking at this article.

I just used a WMI alert to fire for a deadlock. I see that I can create a job to save information using tokens. I was going to ask if there is there any way to avoid this and use a token to populate the @notification_message value of the alert before it is sent. However, I just enabled token replacement - thanks to your gotchas - and it worked - sort of.

It appears the DISCRIPTION also gets populated with the same XML that is displayed for the COMMENT. I was able to set @include_event_description_in to 0.

Unfortunately, the XML is truncated. Looks like I will have to use a step to log the information anyway. Still, some of the "shorter" token values will be of use in the other alerts. However, in WMI alerts, the other tokens do not appears to work as expected.

Database: $(ESCAPE_SQUOTE(A-DBN))
Message: $(ESCAPE_SQUOTE(A-MSG))
Data: $(ESCAPE_SQUOTE(WMI(TextData)))

DESCRIPTION: (None)
COMMENT: Database: \\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER
Message: Variable A-MSG not found
Data: <TextData><deadlock-list> <deadlock victim="process89582f8"> ...

SQL Server should come with a standard alert system in place to send recommended alerts with useful information. While interesting, it is a waste of time to make everybody go through this.

Randy,

Technet gives the getting the Deadlock graph as their example of implementing a WMI alert. It is certainly a good use of WMI alerts.

MSDN says 'System messages (such as 605), as well as user messages added by sp_addmessage, can be modified by using sp_altermessage.' (SQL Server 2000)
(http://msdn2.microsoft.com/en-us/library/aa259612(SQL.80).aspx)

In earlier versions of SQL Server than 2005, the sp_altermessage stored procedure can be used to suppress system messages that have message IDs that are less than 50000. When these messages are suppressed, the messages are not written to the SQL Server Errorlog file. In 2005, this is not possible. This would mean that one couldn't then use alerts on high-severity errors. I'm not sure why anyone would want to do this.
http://support.microsoft.com/kb/922578

I think Robyn is suggesting that we use sp_alterMessage for the reverse purpose, to allow logging on errors less than severity 19, so as to allow alerts to be fired.

Randy,

We feel your pain. As you'll have gathered from the article we didn't find WMI events easy.

I rated the explanatory article on MSDN one star out of five. A comment box popped up. I typed in the following message

'I have solved the mystery of the Roswell incident. These alien intelligences, who fell to earth so tragically, are earning a living at Microsoft in the lonely vigil of devising documentation for MSDN. The problem is that they are unable to stoop to the contemptible intelligence of the carbon-based life-forms that use SQL Server. I suspect, on the other hand, that the WMI alerts were implemented in a needlessly complex manner by baser life-forms entirely.'

the inability in sql 2005 to enable logging for events has been a real backward step for sql server. I'm told that the ability to raise alerts on say error 1205 ( deadlock ) which you can't do in 2005 is being looked into being re-introduced. In software releases into production I would usually enable logging on events such as missing permissions and incorrectly called/named objects - by writing these to the sql log it saved me having to constantly run profiler traces looking for errors. I think your aliens also went to the part of the program dev which handles alerts.

Great article. (as usual :) )

If you want sqlagent to responde faster ...
http://www.sqlservercentral.com/articles/Administration/3177/

---

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

I just discovered SQL Server Alerts in the last couple of days. (I'm not really a DBA, but have to administer a SQL Server 2005 if you know what I mean.) I'm trying to figure out if Alerts are the best way to monitor blocking problems. In general, the goal is to be notified of blocked processes that are blocked for more than 3 seconds. I can see that the Alerts for Performance Counters includes a way to monitor the number of Blocked Processes. I can also see that there are a number of Wait statistics that I could track. I'm guessing that some combination of these counters would get me close to what I want.

But here's the kicker. I also noticed in your article that the "gotchas" list says: "Is the counter value maintained for at least 20 seconds?" I presume this means that SQL Server only looks for performance counters every 20 seconds. (Yes?) So, if I'm looking for a list of blocking problems of duration 4 seconds or longer, then doing Alerts would not be the way to go. Is my logic sound? Or am I missing something?

Do you have any off-the-top-your head advice on the best general approach to take for this task? Would an Agent job that runs every say 3 seconds (and that runs a stored proc that checks for long blocking problems) be a better approach? Or is that approach too resource intensive and not a good idea? It seems like this is a general need that most DBAs would have, but I've done a lot of searching the last couple of days, and I'm having a hard time finding general wisdom advice for this need.

Thanks! - JJ

I would go for the idea of using a stored procedure to run on the scheduler in order to identify blocking processes. The scheduler is very efficient, and as long as you don't mess up with your code, you should be safe using this approach. I seem to remember that MVP Nigel Rivett has a very good procedure on his website to check for blocking processes.
You are right in saying that the performance alerts are much better at giving you more general information and I'm not sure it is a good idea to use it for catching an event with a granularity of less than 29 secs. It may work but it wasn't what it was designed for.

Thanks for the feedback! Much appreciated. I'm going to give it a try and seek out Nigel Rivett's website.
- JJ

Robyn and Phil..(hey where's Phil's pic anyway..although Robyn's is nice to look at..:)) Great article that summarizes the different types of alerts without sinking into a morass of details. I was just playing around with SSMS alerts and decided to look more into the WMA alerts. Thanks for the comprehensive overview.

DRoy

I have tried setting up an Alert on BufferCacheHitRatio and it will not fire. I have set it to Falls Below 100. Any suggestions?

Also after failing to get that to work I tried a PerfMon Alert that writes to the event log. Then I tried to setup a WMI Alert to check for that message. I got the query from the WMI Code Creator from MS and got this error:

SQLServerAgent Error: WMI error: 0x80041010.
The @wmi_query could not be executed in the @wmi_namespace provided. Verify that an event class selected in the query exists in the namespace and that the query has the correct syntax. (Microsoft SQL Server, Error: 22022)

Any ideas?