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:
- Store the results from sp_readerrorlog (last x minutes) into a temporary table
- Remove all records in that table that are on my ignore list (such as database back succeeded messages).
- 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?