I want to address a specific issue in Spotlight, SQL Server performance monitoring tool, with filtering out blocking alert but the principal can be applied to other alarms. The scenario is that you only want to get email alerts for one specific database out of hundreds in your SQL Server Instance.
The brute force way to address this problem is to go to "Configure | Alarms" and open up the "Locks - Blocked Processes" alarm and start adding all the database names that you don't want to get an alarm for to the list. There's got to be better use of your time than sit there and type database name after database name.
And there is...
Spotlight have had the Alarm Actions feature for awhile. It is accessed from "Configure | Alarm Actions." If you are not familiar with it, the Alarm Actions allows to you set actions to take based on the conditions you set. For details on Alarm Actions, you can refer to the Help Contents of Spotlight.
In Alarm Actions, there is condition called:
"The details contain..."
This condition has two inclusive filters:
1. contain any of the following phrases
2. contain all of the following phrases
Choose either one and click on the "Add" button. Then type in the one database name that you wanted to get alerts for.
Here is the caveat and the reason I chose "Locks - Blocked Processes" alarm as an example. This alarm's message (and this alarm only) doesn't state anywhere the database name that it is firing for. It would be all for naught, if we couldn't change this alarm message. So here is how.
- Go to "Configure Alarm"
- From the "Factory Setting (Template)" drop down menu choose the SQL Server Instance
- Look for "Lock - Blocked Processes" and click on the box icon next to it to edit the alarm
- From alarm options screen, uncheck "Factory Settings"
- Under severity description, paste over the existing script with the one below which will include the database name in the alarm message:
Session {{KEY_VALUE}} ({{SQLBlockingList.DBName}}) has been blocked by session {{SQLBlockingList.BlockedBySPID}} ({{SQLBlockingList.blockingdatabasename}}) for the last {{value}} seconds.
Once this is all setup, you should be getting email alerts for only that one database. Lastly, remember to check the action "send email to" in the Alarm Actions. I hope this tip has been helpful. For more information, please visit Spotlight on SQL Server Enterprise.
