Activity overview
Latest activity by brenttempleton
this is the problem with custom metrics -- you can only return an integer value and that is then used to raise the alert -- what we have done is to post a query (sometime EXEC to a stored proc) that will allow to see the details. We the powershell module to write to write the alert details as JSON in a folder, then import and run the SP and email the better details -- prototype of this is working, but it is a good deal to configure. It appears that custom metrics run inside of a transaction or in read-only mode, can't remember what we found when watching via SQL Events. At any rate, table we created to store details never got the data, but when you ran via SSMS (even using the same account as Monitor) the inserts work fine. This is actually probably a good secure feature to avoid bad SQL or worse SQL Injection or something, but still frustrating that custom metrics cannot return a string value. / comments
this is the problem with custom metrics -- you can only return an integer value and that is then used to raise the alert -- what we have done is to post a query (sometime EXEC to a stored proc) tha...
We have a home grown solution based on using canary tables as Brent Ozar blogged about long ago: https://www.brentozar.com/archive/2014/07/monitoring-sql-server-transactional-replication Basically we have added a table to each publication and each subscriber then gets this from DDL replication we then have an agent job that puts new data in on the publisher (and in the case of Merge replication, on each member of the merge). There is an agent job on each subscriber to check the values, if above a threshold (set in a config table per publication), then we put an entry in a common table in our utility db that is on each instance. You then right a custom metric for each publication to have Redgate Monitor alert at different thresholds, can use low, medium, high. We certainly don't have this as a solution that we could just hand off some scripts, we also have all of our replication in our own meta data tables that these canary tables are based on. If you need more details, ping me back and I could get the engineer that designed it to provide more details. Hope this helps Brent / comments
We have a home grown solution based on using canary tables as Brent Ozar blogged about long ago:https://www.brentozar.com/archive/2014/07/monitoring-sql-server-transactional-replicationBasically we...
Not sure if this would work, but recent release adds the ability to call a powershell script when an alert is raised, see Version 13.0.31 Oct 4, 2003 release notes: SQL Monitor 13.0 Release Notes - SQL Monitor 13 - Product Documentation (red-gate.com) I have this on my radar to investigate further, hopefully in early 2024. So I have no experience with it. / comments
Not sure if this would work, but recent release adds the ability to call a powershell script when an alert is raised, see Version 13.0.31 Oct 4, 2003 release notes:SQL Monitor 13.0 Release Notes - ...
SQLMonitor Powershell Change Custom Metric Alert Name and descriptions
Searching the Powershell API documentation and I don't see anywhere that we can create/update/delete custom metrics and alerts. Is this possible?
Dan -- correct, what I want to do is to throw out low durations when calculating the baseline duration, so values less than 1 or 2 seconds in the case that I am working, when this task has "work" to do. I am new to SQL Monitor, wondering if I can setup this job that only when this job runs longer than say 5 minutes, to alert us, but don't want to change for all agent jobs -- I know I can ignore this job completely, which is not also the desired approach. thanks again Brent / comments
Dan -- correct, what I want to do is to throw out low durations when calculating the baseline duration, so values less than 1 or 2 seconds in the case that I am working, when this task has "work" ...
That would not do what I am seeking, that would keep the alert from firing if it takes longer than xxx seconds (120 in screenshot). What I want is do is to eliminate the times from the computed average that are less than 2 second (for example) then the average duration would be an average of runs when it had work to do. So if the last 20 run were as follows (in seconds) 0,0,0,.5,10,5,.5,0,1,18,20,1,0,0,0,12,1,5,1,0 avg of last 10 =3.4 avg tossing out < 2 = 10.8 10.8 is a more realistic time the job spent, so raise alerts when job takes 15 sec or more, right now getting alerts for “normal” run durations, as even 2x avg is still way under normal. I briefly tried to see if I could configure custom metric, but didn’t get very far. thanks / comments
That would not do what I am seeking, that would keep the alert from firing if it takes longer than xxx seconds (120 in screenshot). What I want is do is to eliminate the times from the computed av...
Configure Job Duration logic
we have a good number of jobs that run to see if there is a file present, if so it takes awhile to run, otherwise it takes generally under a second. Is there a way to configure the Job Duration Un...