Make alert configuration levels more discoverable
CONTEXT: In SQL Monitor, you can configure alert behaviour down to the object level (such as the Database or the Job level). This is useful e.g. when we have specific databases with different backup policies to our global configuration, since we can specifiy "special rules" for individual databases.
PROBLEM: It is counterintuitive how to do so, and many people don't spot the left-hand drilldown navigation - this makes you think that you can only configure on the global and the server level! Also, when we have created an override, it's easy to forget so causes surprise when we change the global configuration and the "special" database's behabviour doesn't change.
• Autoexpand the left-hand side navigation, draw attention to it
• Add Disks, Jobs and Databases as child-elements to the left-hand navigation
• show the configuration hierarchy when editing alert settings. Highlight levels where overrides exist. Allow to apply new configuration to multiple levels simultaneously
What do you think about these solutions? Got another idea? Let us know in the comments!
currently under investigation
Thanks for this suggestion, and for the supporting comments.
Regarding the comments around excluding alerts using regex, several alerts now allow you to enter regular expressions to match text you want to ignore, to stop alerts being raised in certain situations.
- Deadlock (XE) alerts can be filtered by database, login name & resource (added in version 8.0.22 released November 13th 2018)
- Blocking Process alert can be filtered to exclude queries with a SQL process names, SQL commands or objects match regular expressions
- Job Duration Unusual alerts can exclude jobs matching regular expressions
- Long Running Query alerts can be filtered to exclude queries with a SQL process names, SQL commands or objects match regular expressions
We will keep this topic open as we are soon hoping to address the main suggestion - making alert configuration levels more discoverable - along with other improvements to alert configuration.
Daniel Brink commented
Please add a regex box, the same as in the "Long running query" alert to allow some jobs to be excluded from being alerted as having unusual running duration by the "Job duration unusual" alert. Currently we have to disable the "Job duration unusual" as it gives false positives on jobs that run every few minutes as they mostly run in zero seconds, but a few times per hour they will run for around 2 minutes. We had to create custom metrics for around +40 jobs and now have to manually maintain them. The change will help us a lot. Thank you
Also for Databases that are in offline mode!!
John Stinchcomb commented
I'm not sure where the database list mentioned here can be found. Obviously there have been a few updates since this was posted, however, I still find myself unable to configure any alerts at the database level.
If would be great to be able to configure SQL Monitor to ignore things like deadlocks on Sharepoint CrawlStore databases or long running queries on a database where the return takes a little longer.
We have a number of databases that are restored overnight therefore go into an 'offline' state, this is normal I would like SQL Monitor to ignore it.
Can you therefore please allow the database offline alert to be configurable so that certain databases are not reported, perhaps within a known time slot.
I know we can set maintenance windows but this stops all alerting.
James Chorlton commented
Very much agree! Just being able to exclude my Log Shipping jobs would be great.
Andy Marter commented
I agree. In our case, we have a TLog backup job that runs every 5 minutes. At 4AM we have an Index Rebuild job. If a very large index meets the criteria to be rebuilt, then it can cause a large log file, which affects the Tlog backup job runtime. I would like a way to suppress this alert based on job name, and/or time of day and customize it to look at more than the last 10 runs for a frequent job like this.
We use Red Gate SQL Backup Pro to log ship our databases to a backup server. During busy times of day, the time taken to perform the log backup can regularly exceed the last 10 runs, or even go way over the customisable threshold.
It would be good if these alerts could be customised based on the combination of server AND database and possibly the fact that it's a log shipping job of which the duration could fluctuate a lot over the day based on work hours and busy times of day.
Thanks for your suggestion Niels.
For the alerts that monitor whether services status started/stopped/paused etc.
Having the option to take the startup type of the service into account would be useful. This way if the service startup type is Disabled then a server specific configuration would not be needed. This would also allow for the option to exclude disabled services or retain the ability to look for a startup type changing and the service being stopped in order to prevent alerts on intentionally disabled services.
Thanks for your suggestion John and Jonathan.
I agree that it would be good to be able to exclude jobs (by name/id) from having Alerts raised. In certain circumstances jobs are always going to fail or run long and having the ability to filter those out will leave the alerts that do get raised easier to deal with.
The situation is that I have a Job that randomly fails throughout the day, we are currently looking for a fix so we know about the issue. However I will receive between 10 and 20 alerts on a daily basis.
It would be nice to have the ability to exclude a job from the alert much like the long running queries at the server granularity for an alert. This would then permit the server to be monitored for failed jobs while a fix is found.
Thanks for your suggestion Robert. We've logged this as SRP-6766
Robert Fazio commented
Databases that are the mirror half of databases using SQL mirroring will be in recovery ALL the time, and you can't back them up, so you have to ingore them.
Aaron Foote commented
I would love this feature. Just installed SQLMon 3.0 (first time user). My current plan is to try and add something to my log running queries so I can filter with a regex. This is ugly, time consuming and complicated. A better way to filter these would be fantastic
Thanks Don. We will definitely try to improve these alerts based on your feedback.
Don Ferguson commented
Thanks for the additional instructions. I now see that some of the alerts can be configured at the database level. Unfortunately the alerts that I am getting flooded with are not on that list. If I could suppress blocking, deadlocks, & long running queries at a database level, that would be more helpful.
Thanks for your message. To supress an alert at database level, please follow these steps:
1. On the Configuration > Alert settings page, go to the left hand navigation.
2. Select and expand SQL Server instance.
3. You would see the list of the databases for this SQL Server instance. Select a database. This will filter the alerts on right hand side which are supported at the database level.
4. Now you can change configuration for this particular database.
Please let me know if I am not clear.
Don Ferguson commented
Thanks Priya, being able to suppress an alert at the database level would help. Unfortunately, I am not seeing this option. I do see the option to suppress at the instance level, but not at the database level. What am I missing?