Control Alerts Using Metadata
CONTEXT: We have a maintenance and backup solution which uses SQL Server as a backend to determine what databases to perform the tasks against, and at what time.
PROBLEM: Some databases do not have maintenance tasks running against them, but still have alerts which appear in SQL Monitor for them (e.g. Index Fragmentation). I don't want to manually exclude these from SQL Monitor, as the number of databases fluctuates, particularly on our testing and development environments.
EXAMPLE SOLUTIONS:
- Provide a means of easily enabling/disabling/changing alerts dynamically using a stored procedure.
Mike Cutts
shared this idea
Daniel Rothig
responded
For your usecase I recommend modifying the maintenance window metadata instead:
UPDATE s SET MW_IsEnabled = 1, —or 0 to enable alerts again MW_Start=0, —midnight MW_Duration=864000000000, —24 hours MW_Monday = 1, MW_Tuesday =1, MW_Wednesday=1, MW_Thursday=1, MW_Friday=1, MW_Saturday=1, MW_Sunday=1 FROM [settings].[SqlServers] s INNER JOIN [settings].[Clusters] c ON c.Id = s.ParentId WHERE c.Name = ‘hostname’ AND s.Name = ’’ —Set to SQL Server name or leave empty for unnamed instancesA restart of the base monitor service will be required after the change
-
Mike Cutts
commented
Thanks for the suggestion. However, we were wanting to be a little more granular than that. We will have databases on an instance where we may not want to perform index maintenance against them, but may want to perform a backup. Therefore, just enabling and disabling a maintenance window (if I understand it correctly) might not work as it would disable all alerting for an instance.
Feel free to correct me if my understanding is wrong on this one.