0

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?

Improve this question

1 Answer 1

Reset to default
1

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

Improve this answer
1
  • I was looking for something like the SQL Server Alerts. In SMSS you cannot really easily define a catch-all, as the linked article mentions, so I looked further. Maybe I should go back and take a look at that again. Monitoring from a different server is not a feasible for me. Using SQLSentry is a too expensive option for the task at hand, although that really depends on the value($) of the data(base).
    – Aloys
    Commented Apr 21, 2017 at 11:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.