Add an option to schedule when custom metrics run against a given database.
Currently when a custom metric is executed can only be controlled by the frequency (daily, hourly, etc).
My primary purpose is for daily. I would like to schedule the time of day a daily metric is run per database, or at least server. The custom metrics I pull back are time sensitive so controlling when the metric is returned is a must.
A more advanced option to this would be going beyond simply choosing the time of day for daily runs, but rather the ability to build a custom schedule of run times. An example might be Custom Metric A runs against Database 1 at 1000, 1400, 1900 and runs against Database 2 at 1030, 1100, 1130, 1200...
Andy Aelbrecht commented
This one has been in the pipeline for almost 10 years; I added my 3 votes here too because indeed, it's very important to be able to choose WHEN a custom metric is being run, especially for the daily ones.
My personal example: We use NetBackup to copy the backup files. We do this - of course - outside of business hours. In the morning, I want to see if everything has been executed as expected.
I have a metric that looks at the contents of the NetBackup logfile to see if certain keywords are there; this works perfectly.
But, the problem is that I need to know how old the log file is, as NetBackup recreates the logfile each day.
I added this check, and it works, but since I can't schedule WHEN the metric runs, I can't use it.
There are more examples for me (and looking at all the comments, I'm not alone).
So now, we are using a workaround where we have a sql agent job doing the checks and sql monitor just reads the value from the table. Of course, this is not cool as we need to add this table and sql agent job to every new instance we spawn...
Steve Hall commented
An added issue, because of this, is that due to server maintenance and suchlike, when the server is restarted I need to restart Base Monitor at the time I need the daily trask scheduled at. 00:30 is no fun for such an avoidable task. And of course, if there are two daily tasks at seperate times, this would make them both run at the same time.
Brian Cassidy commented
There are several 'issues' like this one that is greatly hindering this product from getting the sales it could have in my view.
Ram Vaishnapu commented
We need to schedule the custom metrics jobs to given time, We would like to run these jobs in non-business hours, so database performance will not impact on our production applications. Please consider this one as future enhancement.
Well, I've bumped THIS one up to 3 votes... and would throw the rest of my votes in here if I could.
That's unfortunate-- and really limits the usefulness of the custom metric, to my way of thinking. If I want all alerts from my custom metrics to show up in email at the start of the day I cannot reliably do that. The gold standard would be for Redgate to implement a scheduler with all the flexibility of the SQL scheduler.
mst, to your last comment / question: yes, subsequent to my comment on this a year ago, I found out then that when SQL monitor stops, all times that metrics run are re-set to the time when SQL Monitor is started up. So then every time that happens, I needed to get up at 3Am and recreate my metric that I need to run once a day @ 3AM. I have now set this up is a SSIS package rather and not using SQL Monitor for it :)
It does not make sense to me that this is not a feature in the product - because of this, all the metrics will run at the same time. So say for instance you have 50 custom metrics that will run on all your servers, they all kick off at exactly the same time, which absolutely kills some of your smaller servers. So for me it is actually now a case of not being able to use SQL Monitor as a central place to manage all these things I need to run on my servers because I do not have control over when they run.
This is a "me, too" comment...specifically speaking to the point raised by Theresa. I created a metric that grabs the time it takes to run all my daily full backups on all instances. They are scheduled at 11pm and my custom metric runs once a day. Unfortunately since I created it at 3pm I don't see the data point for yesterday's backups until 3pm today.
It looks like it might change that time of day if I turn off collection and turn it back on at the desired time. (And as she notes-- I'd rather not get up at 1am to do so)
But please confirm-- that doesn't also mean that if SQL Monitor gets stopped for any reason-- that all the collection frequencies set for custom metrics will initialize to start at whatever time of day SQL Monitor gets restarted?
Thanks for your detailed feedback Allen.
Allen Dunn commented
This would be extremly useful as some monitors such as long running queries or Job Duration could be scheduled against a particular database or Job to be ignored at set times.
I manage VLDB databases and when running index rebuilds or integrity checks on these machines the job duration alert among others will always kick in.
If we could schedule these alerts to ignore them at set times or disable the job against a database for a set period this would reduce alerts waiting for you on Monday mornings considerably.
The amount of alerts is always higher on Mondays due to long running maintenance routines that get picked on several alerts which is always a by product of working on VLDB's.
Scheduling against types or databse would negate these excess alerts.
Thanks Theresa for your feedback.
I agree soooo much with this one. I had a custom metric that needed to be scheduled once a day at 3AM and had to get up in the middle of the night to create the metric - CRAZY! :)
I agree that the first step is at least for the daily metrics to have a schedule. It would be nice to be able to customize schedules on all the metrics though. Also would be useful to be able to create more than one schedule on a metric. E.g. if you want to run a metric hourly from 00:00 - 8:00 and also from 13:00 - 19:00.
We have considered scheduling for custom metrics, so it's interesting to see your example of how it would be useful for you. We'll keep an eye out for further comments around this.
Thanks for your suggestion,