I am just starting to experiment with SQL Server Alerts. I set up an Alert on Errors/sec with a threshold of zero, thinking that I would get an email any time an error was written to the log. I got a lot of emails! I raised the threshold to notify me when it was over one per second, and I still get quite a few emails from time to time.
As an example, I get emails that contains something like this:
DESCRIPTION: The SQL Server performance counter 'Errors/sec' (instance '_Total') of object 'MyServerName:SQL Errors' is now above the threshold of 1.00 (the current value is 4.45).
Here is the command for the alert I am using:
EXEC msdb.dbo.sp_add_alert @name=N'SQL Errors',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@notification_message=N'Check the ERRORLOG for details.',
@category_name=N'[Uncategorized]',
@performance_condition=N'MyServerName:SQL Errors|Errors/sec|_Total|>|0',
@job_id=N'00000000-0000-0000-0000-000000000000'
When I look at the log, I don't find any errors. I do find informational messages (a backup completed, etc.) though. Is this alert really "Entries/sec" and not truly "Errors/sec" or am I looking in the wrong place (SSMS | Server | Management | SQL Server Logs | Current) for the actual errors?
All errors are not logged, a insert might be executed that might break a constraint, an error will be raised to the client, but it doesn't mean that it is logged in the error log.
For example if you execute the following t-sql.
RAISERROR ('This is an error!', 16, 1) WITH LOG
the error will be logged in the error log, ommitting the WITH LOG will just cause the error to be raised without logging.
Errors have attributes which you can use to filter on, such as message id, severity, but you are monitor all by the looks of things. Severity might be what you need.