September 2, 2010 at 11:18 am
I've got an Error log table that we're trying to monitor.
Right now we've got a job that runs every 10 minutes and scans the erorr log table for a count of errors that were logged in the past 10 minutes. If that number exceeds a threshold, it bundles them all up and sends out a notification email.
I keep thinking there should be a more natural way for SQL to monitor something like this either a trigger (but counting the number of errors in the past 10 minutes for each error seems excessive) or another way.
Maybe a Broker service that just expires tickets after they've been there 10 minutes and if too many get in the queue sends a note?
I dunno, looking for suggestions here to something other than a scheduled job that scans the table every 10 minutes. 🙂
September 2, 2010 at 11:58 am
I'd probably go with the trigger option.
If you have a job, and it runs every ten minutes, what happens if you get 100 errors 1 second after the job runs? It goes 10 minutes, then tells you about the errors. If you're getting 100 per second, you might need to know about it sooner than 10 minutes later.
If you have a trigger, and set a reasonable rule up for "don't alert me more often than every 10 minutes", you'd get a notification the moment those errors started pouring in.
I don't know if that applies to your situation, but I thought I'd mention that possibility in case it does.
It would be easy enough to use an index on the creation timestamp to make the trigger's query fast and efficient.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 3, 2010 at 8:48 am
Nope that works great, and follows the KISS principle.
I was just looking for something cooler... because well... I wanted something nifty.
But this works great.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply