Options
Alerts don't appear to be firing
JHunter
Posts: 2
Hi, evaluating the SQL Response tool, here are some details:
Repository Machine:
-OS: Windows 2003R2 Server, SP2, x64
SQL Server:
-OS: Windows 2008R2 Enterprise, x64
-SQL: SQL Server 2005, Enterprise, SP3 ,64 bit
A few issues I'm noticing. First, I'm not receiving all the alerts I think I should be. I do receive some (Low Disk Space errors for example) but not all. For example, I have 'tracing' turned on for the SQL Server and I'm monitoring for 'Long running queries' of 2 sec or more. I have profiler pulled up doing the same thing. I have received many hits in profiler for qualifying queries BUT SQL Response hasn't seen one.
Secondly, I'm having the 'This alert is still being written to' issue on some of the alerts.. After reading the forum and a link to a support article on how to handle it, I set the 'Centralized Exceptions' list for Symantec Endpoint Protection to point at the RedGate folders in Program Files and in /All Users/Application Data/. I also destroyed the repository and reinstalled it. Still no alerts. Any other tricks to try?
Repository Machine:
-OS: Windows 2003R2 Server, SP2, x64
SQL Server:
-OS: Windows 2008R2 Enterprise, x64
-SQL: SQL Server 2005, Enterprise, SP3 ,64 bit
A few issues I'm noticing. First, I'm not receiving all the alerts I think I should be. I do receive some (Low Disk Space errors for example) but not all. For example, I have 'tracing' turned on for the SQL Server and I'm monitoring for 'Long running queries' of 2 sec or more. I have profiler pulled up doing the same thing. I have received many hits in profiler for qualifying queries BUT SQL Response hasn't seen one.
Secondly, I'm having the 'This alert is still being written to' issue on some of the alerts.. After reading the forum and a link to a support article on how to handle it, I set the 'Centralized Exceptions' list for Symantec Endpoint Protection to point at the RedGate folders in Program Files and in /All Users/Application Data/. I also destroyed the repository and reinstalled it. Still no alerts. Any other tricks to try?
Comments
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-->
<SysProcessesTime>45</SysProcessesTime>
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
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 support@red-gate.com. You can access the log files via the Icon Menu on the top left hand corner.
Thanks,
Priya
Project Manager
Red Gate Software