Click here to monitor SSC
  • Av rating:
  • Total votes: 51
  • Total comments: 22
Robyn Page and Phil Factor

SQL Server Alerts: Soup to Nuts

19 February 2008

In which Robyn Page and Phil Factor try to get to grips with the difficult subject of SQL Server Alerting, and give you enough detail to put effective alerting systems into your database.

To keep a database system running without hitches, you have to know of anything out of the ordinary that has happened. You need to know about events, error conditions, extreme loading, problems with the hardware, security issues, performance-related conditions, failed processes, rollbacks, deadlocks, long-lasting processes, 'expensive' cached query plans, and a range of other factors that could signal trouble. It is not enough to occasionally browse the error logs: you need to be notified.

“In a sense, a database
experiences discomfort
and pain”

In a sense, a database experiences discomfort and pain. Any organism needs to feel pain in order to take action to do something about the cause of the pain. A database needs to react to ward off the possibility of failure, and the DBA must be able to respond as quickly as possible to signs of potential failure, or when actual failures occur. Alerts are provided to allow the programmer or administrator to react. If you are involved in supporting a high-availability server, you'll know that the skill is in heading off problems at the first signs of trouble. To see those signs, you've got to be on the lookout for them, but you must ensure that they are drawn to your attention. Alerts provide the means to do this.  

Terminology

A few terms before we start: an alert is defined as an automated response to an event. The term "event" in SQL Server is not particularly well-defined. In previous SQL Server versions, an event was anything that could be written to the SQL Server Error Log, and, in later versions, the Windows Application Event Log. Currently, an event seems to mean "any state or condition in SQL Server that can cause an alert".

In any case, events that are generated by SQL Server are also, by default, written to the Microsoft Windows application log. 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.

 You can define alerts, using SSMS, TSQL or SMO, to respond to:

  • SQL Server events  - based on the occurrence of specific errors, or errors of a specific severity, and so on.
  • Performance conditions. - You might define a performance counter on a certain object and set an alert to be fired when a certain threshold condition is reached.
  • Windows Management Instrumentation (WMI) events - an alert is raised when a specific SQL Server event occurs that is monitored by the WMI Provider for Server Events.

In the procedural world, you'd think of the alerting system as the place where you define call-backs to your code on various database conditions. In SQL Server, you define an alert to inform you of errors, events or conditions, so that you can take remedial action. Some events require investigation; some can be dealt with by a script. Others require both. SQL Server alerts can be set up to send a message, or to execute a script. The messages sent to an operator, or administrator, to inform them of an event are referred to as notifications.

Q: Structure or what? A: What

“The original design
was for a crude
but effective system”

Alerts were a late arrival in the evolution of SQL Server. The original design was for a crude but effective system, based in SQL Server Agent, which simply read the SQL Server Error log (now it reads the Application log) and scanned for errors of a particular type or severity. Upon finding one, it could start a TSQL Job and send a message as a notification. This was useful, but provided little information that could be used to take preventative measures before an error happened.

When the means of monitoring SQL Server performance became more sophisticated, two separate notification systems were devised. One of these was based in the operating system, using Performance Monitor, and provided both notifications and command-line executables. The other one used the alerting service of SQL Server Agent, and provided for the triggering of TSQL-based Agent jobs as well as notifications. This latter system has the SQLServer counters, but not the general Operating system ones.

Finally, when WMI was introduced in SQL Server 2005, it seemed an obvious idea to allow the Alerting system to make WMI queries, so a fourth type of Alert was added. The WMI alerting system is comprehensive, and covers aspects that the previous systems missed, but is wildly complicated for a busy DBA to get to grips with.

SQL Server Event Alerts

Very often, the programmer is happy to present the end-user of the database with all error information. This is a bad idea. If, for example, the transaction log has run out of space, there isn't much the end-user can do other than to phone the DBA, panic, or curse the IT department. A simple use of alerts is to allow the DBA or developer to receive a copy of any error that can appear in the error log, as pager alerts, emails, or SMS messages.

Defining Alerts

Events are generated by Microsoft SQL Server and, if they are flagged as being 'logged', are entered into the Microsoft Windows application log and SQL Server Error log. The SQL Server Agent, which is a separate application, reads the application log in order to see if any events that have been written there have been defined by you in an alert. If so, the Agent fires whichever alert defines the error in the most precise way. This means that a specialised alert that is triggered by an event with a particular error number will be chosen rather than a more general one that will fire if it is of a particular severity.

  You can set up an alert from SSMS:

You can raise alerts based on the error number or the error severity. This means that it is pretty easy to define an alert that will fire on every error, for an error of the specified severity level, and give you an email notification.

A curiosity of the SQL Server Event Alerts is that you cannot configure an alert that fires on all events above a particular severity level. To get anywhere near providing a 'catch-all' alert, you need a performance alert, which we'll come to. If you wish to stick to SQL Server Event alerts, you will need to create alerts for every severity level. This gets very boring to do via SSMS, and it is much easier to do in a script. However, it is a job that only needs to be done once for an application in order to get effective alerting for your 'loggable' errors.

You can then go on to create a more sophisticated alerting system, based on alerts that fire only on specific errors. When an error occurs, the alerting system selects the alert that covers the error, rather than one that fires on a type of error.

If you specify the Error number, then you can respond to very specific events, such as

an attempt to query the virtual table, DBLog without being a member of the sysadmin fixed server role or the db_owner fixed database role (9010). The full list of errors, in master.sysmessages, is imposing, and a quick glance through the documentation at the seven thousand odd events will convince you that adding an alert for every error would be impossible See Troubleshooting (SQL Server 2000) System Error Messages

 Often, you will want to specify which database should be the source of events that you want the alert to respond to, rather than accept any events. You can even specify that the error must contain a particular string, such as a user associated with suspicious activity. (this would be useful for searching for a message such as Server user Phil_Factor is not a valid user in database 'CreditCardDetails' )

  Sometimes, events will fire repeatedly over a short period of time. In this case, you may want to know that the event has occurred, but you may not want an alert for every event. To avoid getting repeated messages, you can specify a delay after the alert responds to an event, in which case SQL Server Agent waits for the delay specified before responding again, regardless of whether the event occurs during the delay. You can also disable, and re-enable an alert programmatically.

Logging Errors

SQL Server Agent takes these errors from the error log, so if follows that the errors must be logged in the first place. There is no way of attaching alerts to errors that aren't logged. All error messages with a severity level from 19 through 25 are written to the error log automatically.

So, what if you want to log information messages, or messages of low severity? If you wish to have an alert on any errors that are of a severity less that 19, then you have to modify their entry in the sysmessages table to set them to be always logged. You do this using sp_alterMessage with the WITH_LOG option to set the dLevel column to 128 . If a message has been altered to be WITH_LOG, it is always subsequently written to the application log, however the error happens. Even if RAISERROR is executed without the WITH LOG option, the error that you have altered is written to the application log, and is therefore spotted by the alert. There are good reasons for wanting to do this, as it will then log, and optionally alert you to, syntax errors that are normally seen only by the end-user.

  You can force any error that is triggered programmatically to be written to the error log by using the WITH LOG parameter with the RAISERROR command. So, with a user-defined error severity (9) you can log an incident, cause an alert to be fired, which in turn emails someone, or runs a job, simply by using RAISERROR. Naturally, because the job that responds to the alert can be run by the Agent under a different User, you do not need to assign unsafe permissions to the ordinary user. You can use xp_LogEvent if, as is likely, you do not want the user to see the error. (Only the Raiserror call can utilize the 'PrintF' formatting  placeholders in the error messages, so logging the error with a formatted message using xp_logevent results in a literal recording of the message, string format identifiers and all.)

Error Severity Levels

Errors with the higher severity levels are something you need to know more about, especially if a user is going to phone you anyway after seeing a scary message in his application. The severity levels provide the best means of creating a general-purpose alerting system, supplemented by special alerts for common problems, such as TempDB running out of space.

 Severity level 10 - 16
are generally generated though mistakes by users, problems in the TSQL scripts and stored procedures executed by users. A number of programming errors and input problems can cause this sort of error. The problem here is that they aren't logged and it isn't normally possible to get them logged.
Severity levels from 17 (Insufficient Resources) and 18 (Nonfatal Internal Error Detected)
are generated by resource or system errors; the user's session is not interrupted.
Severity levels from 17 through 19
require the attention of the DBA, who can then get more information by executing DBCC CHECKDB (database) to find out more about the extent of the damage. A severity level of 19 (SQL Server Error in Resource) will stop the current batch.
Severity levels 20 (SQL Server Fatal Error in Current Process), 21 (SQL Server Fatal Error in Database dbid Processes), 22 (SQL Server Fatal Error Table Integrity Suspect), 23 (SQL Server Fatal Error: Database Integrity Suspect), 24 (Hardware Error) and 25
Indicate system problems. Errors messages with a severity level of 20 or higher are considered fatal errors and terminate the client connection. Errors messages in this range may affect all of the processes in the database, and may indicate that a database or object is damaged. These errors are fatal to the process running at the time. The process will record diagnostic information, and then terminate.

Tokens: passing information from alert to job

Tokens are extraordinarily important for creating anything but the very simplest alerting system. If you use an alert to fire a job, you then get a number of useful bits of information passed to the job, which can then be used to pass more information to whatever system you put in place to remedy the problem.

This information is provided to the job as macros or 'Tokens'. Except for the WMI token, these have been in place since SQL Agent 7.0 and 2000.As these token are not well-known I'll list them here:

Token

Description

(A-DBN)

The Database name is passed to the Job from the alert in this macro

(A-SVR)

The Server name is passed to the Job from the alert in this macro

(A-ERR)

The Error number is passed to the Job from the alert in this macro

(A-SEV)

The Error severity is passed to the Job from the alert in this macro

(A-MSG)

The Message text is passed to the Job from the alert in this macro (this will include the error number and severity as strings)

(DATE)

The Current date (in YYYYMMDD format).

(INST)

The Instance name. For a default instance, this token is empty.

(JOBID)

The Job ID

(MACH)

Computer name.

(MSSA)

Master SQLServerAgent service name.

(OSCMD)

Prefix for the program used to run CmdExec job steps.

(SQLDIR)

The directory in which SQL Server is installed. (By default, this value is C:\Program Files\Microsoft SQL Server\MSSQL.)

(STEPCT)

Step Count: A count of the number of times this step has executed (excluding retries). (Can be used by the step command to force termination of a multistep loop.)

(STEPID)

Step ID.

(SRVR)

The Server. Name of the computer running SQL Server. If the SQL Server instance is a named instance, this includes the instance name. This can be different from the server that was the source of the event

(TIME)

Current time (in HHMMSS format).

(STRTTM)

The time (in HHMMSS format) that the job began executing.

(STRTDT)

The date (in YYYYMMDD format) that the job began executing.

(WMI( property ))

WMI Property. For jobs that run in response to WMI alerts, the value of the property specified by property. For example, $(WMI(DatabaseName)) provides the value of the DatabaseName property for the WMI event that caused the alert to run.

Tokens are replaced at runtime like traditional Macros, before the job step is executed. They are, unfortunately, case-sensitive, and you get unexplained errors if you leave spaces around brackets. They need to be handled with care because, if you are not aware of them, then they can cause unintended chaos in your code. They are notoriously hard to debug with anything more sophisticated than a 'PrintF'. I usually write the results of the macro substitution to a table, before executing them as SQL, just to check that all is well.

As tokens are strings, you need to use special macros to ensure that they execute. Actually, you now have to use them even if you don't think you need them. This is why the ESCAPE_NONE macro is provided.

SQL Server Agent Escape Macros

Escape Macros

Description

$(ESCAPE_SQUOTE( token_name ))

Escapes single quotation marks (') in the token replacement string. Replaces one single quotation mark with two single quotation marks.

$(ESCAPE_DQUOTE( token_name ))

Escapes double quotation marks (") in the token replacement string. Replaces one double quotation mark with two double quotation marks.

$(ESCAPE_RBRACKET( token_name ))

Escapes right brackets (]) in the token replacement string. Replaces one right bracket with two right brackets.

$(ESCAPE_NONE( token_name ))

Replaces token without escaping any characters in the string. This macro is provided to support backward compatibility in environments where token replacement strings are only expected from trusted users. For more information, see "Updating Job Steps to Use Macros," later in this topic.

  Be warned: in SQL Server 2005, these tokens are disabled 'for security reasons'. Before you use these tokens, you must enable their use by right-clicking SQL Server Agent in Object Explorer, selecting Properties, and on the Alert System page, selecting 'Replace tokens for all job responses to alerts'.

In SQL Server 2005, the SQL Server Agent job step token syntax has changed. Now, an escape macro must now accompany all tokens used in job steps, or else those job steps will fail (see http://msdn2.microsoft.com/en-us/library/ms175575.aspx) . It is therefore best to write all tokens with escape macros around them so as to be backward-compatible. An example should make that clear.

Here is a job step that writes any error passed to it from a SQL Server event, and includes the full text of the error, with the severity and error number, so you can put it into your database application's own private log. You'll see that it includes the database and server as well, just in case you aggregate these messages into an enterprise-wide report.

INSERT INTO activityLog (TYPE,logstring,comment)

   SELECT 204,'SQLServerError','on database: $(ESCAPE_SQUOTE(A-DBN)), Server: $(ESCAPE_SQUOTE(A-SVR)): $(ESCAPE_SQUOTE(A-MSG))

SQL Server Performance Alerts

It is not just errors and events that can trigger alerts. You can specify an alert that will fire in response to a particular performance condition. In this case, you specify which performance measure the alert should monitor, a threshold for the alert, and the behavior of the counter that will trigger the alert. To define a performance alert, in SSMS, you set the:

  • 'Object' or type of performance measure to be monitored.
  • 'Counter', or attribute of the measure to be monitored.
  • Specific 'instance' (if any) of the attribute to be monitored.
  • The threshold for the alert (a number)
  • The behavior that produces the alert (e.g. 'falls below', 'becomes equal to', or 'rises above' a number specified for performance condition counter.

By way of example, if you wanted to set an alert to occur on 'SQLServer:Locks' (object) when the Lock Requests/sec (counter), the number of new locks and lock conversions per second requested from the lock manager, exceeds 10, then you would choose 'rises above' (behaviour) and specify 10 (threshold) as the value.

You might want to set an alert to occur when 'SQLServer:Transactions' (object), 'Free Space in TempDB (kb)' (counter) 'falls below' (behaviour) 200 (threshold) so you can see the cause, such as a long-running process holding a temporary table or a process creating multiple 'permanent temporary' tables (see the excellent Troubleshooting Insufficient Disk Space in tempdb)

Another obvious alert is one that warns of an error being written to the error log. This uses SQLServer:SQL Errors object, with the Errors/Sec counter with the User Errors instance. This will fire an alert for all SQLServer errors.

  There are a number of 'objects' on which performance alerts can be set:

  • SQLServer:Access Methods
  • SQLServer:Backup Device
  • SQLServer:Broker Activation
  • SQLServer:Broker Statistics
  • SQLServer:Broker / DBM Transport
  • SQLServer:Buffer Manager
  • SQLServer:Buffer Node
  • SQLServer:Buffer Partition
  • SQLServer: Catalog Metadata
  • SQLServer:CLR
  • SQLServer:Cursor Manager by Type
  • SQLServer:Cursor Manager Total
  • SQLServer:Database Mirroring
  • SQLServer:Databases
  • SQLServer:ExecStatistics
  • SQLServer:General Statistics
  • SQLServer:Latches
  • SQLServer:Locks
  • SQLServer:Memory Manager
  • SQLServer:Plan Cache
  • SQLServer:SQL Errors
  • SQLServer:SQL Statistics
  • SQLServer:Transactions
  • SQLServer:User Settable
  • SQLServer:Wait Statistics

The number of individual counters within these objects is just too many to list. If you take just one of these objects (SQLServer:General Statistics ) you'll see:

  • Counters for Active Temp Tables
  • Event Notifications Delayed Drop
  • HTTP Authenticated Requests
  • Logical Connections
  • Logins/sec
  • Logouts/sec
  • Mars Deadlocks
  • Non-atomic yield rate
  • Processes blocked
  • SOAP Empty Requests
  • SOAP Method Invocations
  • SOAP Session Initiate Requests
  • SOAP Session Terminate Requests
  • SOAP SQL Requests
  • SOAP WSDL Requests
  • Temp Tables Creation Rate
  • Temp Tables For Destruction
  • Trace Event Notifications Queue
  • Transactions
  • User Connections

Faced with the huge variety of  event counters that can be used to fire alerts, there really is no alternative but to leap headfirst into Books Online. Be assured, that there are counters for every imaginable conditions and some that are unimaginable.

A curious behavior of some of these alerts is that the alerts often fire continuously (every twenty seconds) even when the counter is no longer at the specified level. I take the precaution of disabling, and then re-enabling the counter in the associated job if this happens. It is difficult to reconcile this with the documentation and I'm curious as to why this happens.

Windows Management Instrumentation alerts

The WMI alert provides far more information than is possible by using the previous types. Be warned, though, that you need to allow plenty of time to get WMI alerts working. This is not for the faint-hearted.

SQL Server Agent can, in SQL Server 2005, raise alerts after the occurrence of Windows Management Instrumentation (WMI) events. SQL Server Agent can respond to SQL Server WMI events, as well as to WMI events raised by the operating system and other WMI providers. SQL Agent can even be persuaded to respond to remote WMI events but Microsoft does not encourage this.

There are a vast host of WMI variables that are available as alerts. These are to be found documented as "Trace Events for Use with Event Notifications". These WMI variables are what the Agent uses as a mechanism for accessing WMI tokens.>

To create a WMI alert, you must specify the WMI namespace that is provided to query for events. Each instance of SQL Server 2005 has its own WMI namespace, which defaults to:
\\.\root\Microsoft\SqlServer\ServerEvents\instance_name.

The instance_name defaults to MSSQLSERVER in a default installation of SQL Server.

To create a WMI event, you need to specify the WMI variable that will be used. WMI variables are accessed by a variety of SQL syntax called 'Windows Management Instrumentation Query Language' (WQL). SQL Server Agent submits a WQL request, receives WMI events, and runs a job in response to the event. Several Service Broker objects are involved in processing the notification message, but the WMI Event Provider handles the details of creating and managing these objects. Because of the underlying use of Service Broker, it must be enabled in msdb as well as in the database you are monitoring, before you can hope for SQL Server Agent to receive WMI events.

A WQL query references the event to set up the WMI Alert, using "SELECT * FROM <my_event>" syntax. To make this work, one must configure SQL Server Agent to "Replace tokens for all job responses to alerts"  By checking the appropriate box. (Microsoft say that this is a security precaution).

SQL Server Agent is able to parse and replace strings at run time in job step commands, output files, and alerts by using tokens. In SQL Server 2005 the syntax has changed from "[X]" to "$(X)" where X is the token name. As a result, token names no longer conflict with existing database objects.(see What's New in SQL Server Agent for Microsoft SQL Server 2005). One must also use macros for WMI tokens as documented in http://support.microsoft.com/kb/915845/en-us '...when the jobs contain job steps that use tokens&#133;'

The Pile of Logs

  The SQL Server error log
This was the original log for SQL Server when it morphed from Sybase. As well as events, the SQL Server startup conditions, the  results of backup and restore operations, automatic recovery messages,  kernel messages,  errors in user connections, server-level error messages, the results of batch commands, or other scripts and processes are written to the SQL Server Error Log. The SQL Server Error log is written out as a plain ASCII file and is located at ...
....Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.
 
A new error log is created each time an instance of SQL Server is started, although the sp_cycle_errorlog system stored procedure can be used to cycle the error log files without having to restart the instance of SQL Server. Typically, SQL Server retains backups of the previous six logs and gives the most recent log backup the extension .1, the second most recent the extension .2, and so on. The current error log has no extension. The errors in the Error log duplicate those in the Application event log,. Messages about and any automatic recovery strategies attempted are written into the SQL Server Error log..
SQL Server Agent Error Log
 
SQL Server Agent has its own error log. This  records warnings and errors about potential problems, such as "Job <job_name> was deleted while it was running.&#8221; or "Unable to start mail session." Error messages can be sent as notifications
 
SQL Server maintains up to nine SQL Server Agent error logs. Each archived log has an extension that indicates the relative age of the log. For example, an extension of .1 indicates the newest archived error log and an extension of .9 indicates the oldest archived error log.
 
Like the SQL Server ErrorLog, the SQL Server Agent ErrorLog is stored in a text file on the server. usually in the subdirectory \MSSQL\Log under the location were the SQL Server Instance's code is installed. 
SQL Server Setup Log
The SQL Server 2005 Setup log  is usually located at %ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt. This  SQL Server Setup Log
Will show a component failure, the details of which will be found in that component's log, which is  in the ...
...%Program-Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files directory.
SQL Server Agent service history log
The progress and outcome   of all jobs is stored in system tables in msdb. These also contain a description of any errors, so should be monitored.
the Application Event log,
SQL Server can also use write to the Application event log,  Any application-generated system errors are written into the Application log. The Windows application log provides an overall picture of events that occur on the Windows operating system  because they  are a common repository for system and application errors. All errors written to the SQL Server Error Log are, by default, duplicated to the Application event log. This is useful because the Event Viewer logs are addressable from remote machines, allowing centralized error handling in enterprise systems
Security Event log
Because user connection issues are sometimes rooted in the Windows NT security system,  the DBA will need to check this. These problems are usually easily recognized when SQL Server reports that access to a specific resource has been denied.
System Event  log
SQL Server problems can be caused by the failure of other services, which will be recorded in this log.
 

 Alert Gotchas

  1. Has the account the SQLServerAgent services runs under got sufficient rights to do what you want?
  2. Is the alert enabled?
  3. Is The SQL Server Agent service running.
  4. Does the event appear in the Event log?. If not, then use sp_altermessage to force it to be logged
  5. If you're using xp_logevent to trigger events, be aware that xp_logevent does not trigger an alert unless the @database_name for the alert is 'master' or NULL.
  6. When did the alert last work? Check the history values of the alert to determine the last date that the alert worked.
  7. Is the counter value maintained for at least 20 seconds?
  8. If things don't work as expected, Check the SQL Server error log, job history and SQL Server Agent error log for errors
  9. Have you specified the message recipient correctly?
  10. Have you switched on the use of tokens in Job Steps? (see above)
  11. Are you enclosing your tokens in 'Escape Macros' (See above)
  12. Is the client, on which a WMI management application originates, a Windows authenticated login or group in the instance of SQL Server specified in the application's connection string of the application?
  13. Has the calling application cot the correct permissions in the database to create the required event notifications.
  14. Have you enabled Service Broker before using WMI alerts? (use
    SELECT name, is_broker_enabled, service_broker_guid FROM sys.databases;
    ..to check this

Essential reading

 

Robyn Page and Phil Factor

Author profile:


Robyn Page has worked as a consultant with Enformatica and USP Networks with a special interest in the provision of broadcast services over IP intranets. She was also a well known actress, being most famous for her role as Katie Williams, barmaid and man-eater in the Television Series Family Affairs. She is currently having a career break to raise a young family.

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 20 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

Search for other articles by Robyn Page and Phil Factor

Rate this article:   Avg rating: from a total of 51 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Alerts are not based on the log files
Posted by: Anonymous (not signed in)
Posted on: Wednesday, February 20, 2008 at 6:58 AM
Message: "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?

Subject: Re: Alerts are not based on the log file
Posted by: Phil Factor (view profile)
Posted on: Wednesday, February 20, 2008 at 10:39 AM
Message: 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!

Subject: Are you sure Alerts are not based on the log file?
Posted by: Robyn Page (view profile)
Posted on: Wednesday, February 20, 2008 at 11:10 AM
Message:
 --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

Subject: Re: Are you sure Alerts are not based on the log file?
Posted by: Randy In Marin (view profile)
Posted on: Thursday, February 21, 2008 at 11:14 AM
Message: 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.

Subject: Job Tokens Only In Job Steps?
Posted by: Randy In Marin (view profile)
Posted on: Thursday, February 21, 2008 at 12:45 PM
Message: 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.

Subject: Re: Are you sure Alerts are not based on the log file?
Posted by: WBrewer (view profile)
Posted on: Thursday, February 21, 2008 at 12:57 PM
Message: 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.



Subject: Struggling with WMI alerts
Posted by: Phil Factor (view profile)
Posted on: Thursday, February 21, 2008 at 1:11 PM
Message: 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.'

Subject: alerts
Posted by: grumpy old dba (view profile)
Posted on: Wednesday, February 27, 2008 at 10:18 AM
Message: 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.

Subject: SQLAgents responce interval ...
Posted by: ALZDBA (view profile)
Posted on: Tuesday, March 04, 2008 at 8:43 AM
Message: Great article. (as usual :) )

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

Subject: Alerts And Blocking - Good Way To Go?
Posted by: JJEugene (view profile)
Posted on: Thursday, March 06, 2008 at 1:34 PM
Message: 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

Subject: Re: Alerts And Blocking - Good Way To Go?
Posted by: Phil Factor (view profile)
Posted on: Sunday, March 09, 2008 at 11:44 AM
Message: 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.

Subject: Re: Alerts And Blocking - Good Way To Go?
Posted by: JJEugene (view profile)
Posted on: Monday, March 10, 2008 at 10:45 AM
Message: Thanks for the feedback! Much appreciated. I'm going to give it a try and seek out Nigel Rivett's website.
- JJ

Subject: Good Article
Posted by: DRoy (view profile)
Posted on: Sunday, March 23, 2008 at 1:54 PM
Message: 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

Subject: Performance Alert and WMI Alert
Posted by: Jack Corbett (not signed in)
Posted on: Friday, April 04, 2008 at 4:00 PM
Message: 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?

Subject: Help with this in SQL 2000
Posted by: Anonymous (not signed in)
Posted on: Tuesday, May 06, 2008 at 11:15 PM
Message: Hi, Quite new to this and have not managed to get this working in SQL 2000. Just wondering if some could provide a link to some documentation on passing Tokens to jobs in 2000 like the example provided for 2005? I tried using the example adjusted to using only square brackets etc but keep getting an error 120 message about the amount of columns not matching the select, even though this is not the case. I suspect my syntax is out.

Subject: sp_alterMessage WITH_LOG does not work in SS2005
Posted by: Kevin English (not signed in)
Posted on: Tuesday, May 13, 2008 at 3:19 PM
Message: sp_alterMessage WITH_LOG does not work in Sql Server 2005 for messages with error numbers less than 50000. It did work in SQL Server 2000.

I also tried the sequence:
sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
update sysmessages ...

this did not work either.

Subject: How to capture a timeout from dotnet ?
Posted by: Anonymous (not signed in)
Posted on: Monday, June 30, 2008 at 2:55 PM
Message: We are trying to determine comment exactly what happens when a timeout from .NET occurs and how it's setup. Does it set a connection timeout? An execution timeout? Maybe there's no lock timeout at all. Is there some alert that can capture this dotnet timeout? To test this I attempted to run this code below.

Running the code, Profiler shows SQL:BatchCompleted event class for the wait command with and error "2 - abort". Immediately after the line, I see an "Attention" event class with no additonal information.

.net SqlClient Data Provider

Snippet of code:

public static void RunSnippet()
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);Initial Catalog=dabase;Integrated Security=False; uid=ulog; password=upass"))
{
using (SqlCommand command =
connection.CreateCommand())
{
SqlTransaction transaction = null;
command.CommandTimeout = 10;

try
{
connection.Open();

transaction = connection.BeginTransaction();

command.Transaction = transaction;

command.CommandText = "WAITFOR DELAY '00:00:40'";
command.ExecuteNonQuery();

transaction.Commit();
}
catch
{
WL("Conn: " + connection.State.ToString());
WL("Transaction Null: " + (transaction == null));
WL("Transaction Conn: " + (transaction.Connection.State.ToString()));
transaction.Rollback();
throw;
}
finally
{
connection.Close();
}
}
}

Subject: SQL Server Alerts based on data change in a table
Posted by: msood (view profile)
Posted on: Thursday, February 03, 2011 at 9:03 AM
Message: Hello Robyn and Phil,

I'm impressed with this article as it very clearly explains the concept of SQL Server alerts. Thanks for writing!
I have a quick question - can these alerts be set based on a data value change in a table? e.g., if the value of a Status column for a particular userid changes in a table, can I send an email alert to assigned people? This is a feature of Event Studio in Cognos 8, and I'm trying to see if SQl Server has similar functionality.
Thanks in advance!

Subject: SQL Server Alerts based on data change in a table
Posted by: msood (view profile)
Posted on: Thursday, February 03, 2011 at 9:32 AM
Message: Hello Robyn and Phil,

I'm impressed with this article as it very clearly explains the concept of SQL Server alerts. Thanks for writing!
I have a quick question - can these alerts be set based on a data value change in a table? e.g., if the value of a Status column for a particular userid changes in a table, can I send an email alert to assigned people? This is a feature of Event Studio in Cognos 8, and I'm trying to see if SQl Server has similar functionality.
Thanks in advance!

Subject: SQL Server Alerts based on data change in a table
Posted by: msood (view profile)
Posted on: Thursday, February 03, 2011 at 11:52 AM
Message: Hello Robyn and Phil,

I'm impressed with this article as it very clearly explains the concept of SQL Server alerts. Thanks for writing!
I have a quick question - can these alerts be set based on a data value change in a table? e.g., if the value of a Status column for a particular userid changes in a table, can I send an email alert to assigned people? This is a feature of Event Studio in Cognos 8, and I'm trying to see if SQl Server has similar functionality.
Thanks in advance!

Subject: SQL Server Alerts based on data change in a table
Posted by: msood (view profile)
Posted on: Thursday, February 03, 2011 at 12:59 PM
Message: Hello Robyn and Phil,

I'm impressed with this article as it very clearly explains the concept of SQL Server alerts. Thanks for writing!
I have a quick question - can these alerts be set based on a data value change in a table? e.g., if the value of a Status column for a particular userid changes in a table, can I send an email alert to assigned people? This is a feature of Event Studio in Cognos 8, and I'm trying to see if SQl Server has similar functionality.
Thanks in advance!

Subject: Alerting and Permissions
Posted by: mgs21168 (view profile)
Posted on: Tuesday, February 07, 2012 at 11:00 AM
Message: Robyn and Phil,

Thank you for the informative article. I appreciate immensely that you go beyond simply reposting Microsoft documentation.

I hope you might have a suggestion for me; I'm working with SQL 2008.

I have a few T-SQL batches that are called by an app, and would like to provide some traceability without needing 'sysadmin' or similar high-level permissions. For my needs, traceability means simple messages like "Batch xyz started," "Batch xyz completing," etc.

I tried RAISERROR with a severity of 1 thinking "0-18, any user," but it appears that adding WITH LOG takes precidence and requires sysadmin. Likewise 'sp_OACreate' requires sysadmin. I also looked at xp_logevent, but it requires db_owner on 'master.' I'm considering a "tracing" table in the database, but would like to externalize the trace messages, if possible.

Any suggetions?
Thank you,
-- Martin

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.