Trigger Self Healing Script and Auto Close
It would be great if SQL Monitor could invoke a SQL script upon an alert threshold being crossed and then take action within itself (ie. clear itself, notify a team, etc) based on the value returned (0/1) or default to an action if there is an issue with the script execution.
This type of functionality would help administrators have a first line of defense before truly getting paged on an alert such as a drive filling (perhaps you have txt files you can purge or shrink logs (though I don't necessarily condone this, but you get the idea!)
Michael Ansaldi
shared this idea
-
Brian Shinkle
commented
Agreed. Powershell scripts should surface the same data via $alertData that is visible in the UI.
This would allow an alert to trigger a powershell script that would have access to enough data to resolve the problem.
If the powershell script returns a specific value, this should trigger closure of the alert (as if the alert had been closed via the UI).Consider this example:
1) Alert is raised for server waits. The alert is configured to trigger a user-created powershell script
2) The alert passes $alertData to the script, which includes the following data points that are visible in the UI when viewing alerts:
- query IDs for the top queries during the time window
3) The powershell script can take action on the query in question, potentially by either killing the session, recompiling the procedure, or removing the plan from the procedure cache.
4) Upon completion, the powershell script would return a code that instructs Redgate monitor to clear the alert.In this day and age of automation, DBAs should be provided the tools to build self-healing alert monitors. Without this, we would have to build our own monitoring code outside of Redgate Monitor.
-
Michael Ansaldi
commented
Another example of this type of functionality being helpful would be for failover scenarios (clustered or AG), being able to run a script which serves as a general health check (criteria defined by administrators) and then close the alert out based on a return code would be nice.