SQL Server – Monitoring Rows in a Table

sql server

I'm trying to achieve the following tasks in SQL Server. I have a table that I need to monitor its growth. The table shouldn't be containing more than X amount of rows, if there are that many amount of rows I need to know.
I was planning on creating a stored procedure that would return to me the amount of rows found in the table that I want to monitor.
The stored procedure would also evaluate the number of rows in the table and if its greater I want to store the time that this happen in a table that I have designated for this purpose(this will give me a clue if this is happening at certain times).
Then I would put this SP in a sql agent job and run it every hour, and have it email me when the event occur.

However I'm not sure if this is the best way to monitor how many rows there are in the table. I was thinking I could also make a trigger on the table and on every insert have it check the amount of rows that there are, and then write it to the monitoring table, and then I would check this table every so often with a sql agent job and have it email me as well.

Is there a way I could make a job email me as soon as the table goes over the threshold limit that i have set? I'm not sure what would be the best way to implement this.

Best Answer

If you have all the logic that you want to check within your stored procedure, then storing the time "that it happened" would only be storing the time you actually checked the table. If you are executing this stored procedure every hour via an Agent job then you only know it happend withing that previous hour.

Using a trigger on the inserts would provide a more detailed timestamp as to when the table went over your threshold.

Now for immediate notification I would likely look at SQL Agent Alerts. I don't work with triggers all that much, but would think you could do something similar to the following logic in the trigger:

  1. Check row count of table
  2. If over threshold use RAISERROR to generate event text (custom sys.message) to include your table name
  3. Write entry to your monitoring table, with a timestamp

Now you would need to create a custom message using sp_addmessage (BOL), so you can use it with the RAISERROR. Then your SQL Agent Alert would just check for that message text. When the alert is fired (meaning it caught the message) you configure it to notify you by email.

Related Question