Sql-server – Setup mail notifications on SQL Server (severe) errors

alertsdatabase-mailsql serversql-server-2016

I want to be notified by email whenever an error occurs on our production database (e.g. failed database backups, engine crashes due to corruption etc). I could not find built-in support for this, so I wrote my own stored procedure that runs every 5 minutes.

My stored procedure takes the following steps:

  1. Store the results from sp_readerrorlog (last x minutes) into a temporary table
  2. Remove all records in that table that are on my ignore list (such as database back succeeded messages).
  3. If there are any entries left, send an email to me (containing the last x minutes of the error log).

While this appears to work fine there is a major flaw: If the SQL Agent Service is not running I'm not getting notified about errors.

Therefore I'm wondering, there must be a better way to do what I want?

Best Answer

If the SQL Agent Service is not running I'm not getting notified about errors.Therefore I'm wondering, there must be a better way to do what I want?

Few options

1.Monitor from different server 

IF EXISTS(
SELECT 1
FROM MASTER.dbo.sysprocesses
WHERE program_name = N'SQLAgent - Generic Refresher')

2.Use a Third Party Monitoring service like SQLSentry

Also i don't see a reason why you need to Parse SQL error log .SQLServer alerts can be used to catch most of the errors/Performance conditions

below article has more details on how to set up alerting for different scenarios

https://www.simple-talk.com/sql/database-administration/sql-server-alerts-soup-to-nuts/

And this article also shows ,few more ways to monitor using audits

Related Question