You are not getting Long Running with duration of 2 seconds because by default we poll only every 45 sec (for Long Running Query). So for a query to qualify for 'Long Running Query' alert it should ideally pass at least twice when we poll. So SQL Response can at best catch a query of duration 46 sec but it will always catch anything which is running for more than 90 sec (which is double the polling frequency). E.g. if you always want to catch a query which is of duration 40 sec or more then you have to reduce the polling frequency to 20 sec.
You can look at the config.xml (which is in install directory where you have installed your Alert Repository). This is by default should be at this location C:\\Program Files\\Red Gate\\SQL Response 1\\Alert Repository. Then look at this particular section:
<!-- This will affect the polling frequency for these alerts; Long running query and Blocked SQL Process - setting to every 45 seconds-->
You can change this to modify the polling frequency. But I would not recommend to set this to a very low value as this will have impact on the performance of your server.
If you want to test SQL Response for a Long Running Query then may be the easiest thing would be to use a waitfor delay statement. Something like
- Code: Select all
Waitfor delay '00:02:00'
Regarding your second problem.. if you have already tried the all the steps in the KB article and still the new alert occurrence has the same problem then please send the Alert Repository log files to email@example.com
. You can access the log files via the Icon Menu on the top left hand corner.