sql-servermonitoringjobssql-job

How to monitor and alert a table row value in SQL Server for a maximum value

I have two rows in one table. One has the start value of an ID#1 with 100000 and the other with an ID#2 1000000

Is there any possibility to monitor and trigger an alert on Microsoft SQL Server if the values were reaching for ID#1 999999 or for ID#2 9999999?

The ID get updated by an external code. Here I need to monitor the records and notify once condition occurred. I wonder if it is possible to achieve the task from database server side?

I already searched in MS SQL MMS and on the MS SQL Documentation but couldn't find a solution.

Solution

  • Set up a recurring SQL job in the SQL Job Agent that checks for the existence of a record and sends you an e-mail if it's there.

     IF EXISTS (SELECT 1
           FROM   tablea
           WHERE  id >= 999999)
  EXEC Sp_send_dbmail; -- this needs to be expanded of course

IF EXISTS (SELECT 1
           FROM   tableb
           WHERE  id >= 999999)
  EXEC Sp_send_dbmail;