How To have a faster response to your alert when using SQLAgent

We use alerts to launch jobs which are not owned by the standard SQL Server users, but by the system administrators because of the need for xp_cmdshell. With SQL2005 we are implementing SQLAgent proxies, but the applications run with less authority and don't own any jobs. So for the moment the alert-system is still in use. You can find more information in my script at : help to tighten use of cmdshell or sp_start_job

Since our implementation of SQL2005, we received notifications in SQLAgent.OUT like : 2007-07-23 14:02:49 - + [425] delay_between_response attribute (5 sec) of alert (18) is less than poll interval for this alert (20 sec)

So this means that SQLAgent will only respond to events in the eventlog every 20 seconds.

I couldn't find info about it in Books Online, so I launched threads in several forums to find out how this can be altered, but received no satisfying answer.

Finally I started digging into SQLAgent's settings for the quest of the holy poll.

There are many registry keys SQLAgent has in use, and one of the is called : EventLogPeekInterval. I knew, that if I wanted SQLAgent to respond to an alert, I needed to have it logged into the Windows eventlog, so this might be the knob to switch.

This registry key had a value of HEX 14 (20 decimal).

I altered it to HEX a (10 decimal).

I stopped SQLAgent and started it immediately after it did shut down.

The result that I received was the one I hoped for.

Now I got the message : 2007-07-23 14:10:39 - + [425] delay_between_response attribute (5 sec) of alert (18) is less than poll interval for this alert (10 sec)

So it was indeed the switch I had been looking for.

I experimented a bit more and I found out that the Microsoft's SQLAgent team did build in some restrictions: This registry key's value needs to be bigger than 9. If you put a value in it, lower than HEX a (10 decimal), SQLAgent neglects it at startup time and uses the default 20 second peek interval.

I tested the value for an upper limit to find out when it would switch back to 20 seconds. When I provided HEX e11 (3601 decimal) it finally re-applied it's default. The value HEX e10 (3600 decimal) still worked fine.

So you can set SQLAgent to poll the eventlog between 10 and 3600 seconds. (between HEX a and HEX e10)

If you need responses in an interval lower than 10 seconds, maybe Service Broker is the way to go. My code is shown below:

-- Script to alter the needed registry key
declare @RegValue int
set @RegValue = 10 -- provide dec. value in seconds between 10 and 3600
exec xp_instance_regwrite @rootkey='HKEY_LOCAL_MACHINE',
@key=N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
@value_name='EventLogPeekInterval',
@type='REG_DWORD',
@value=@RegValue

My conclusion

On our SQL Servers I'll lower this setting to 10 seconds only on servers where this alert system is in use.

Keep in mind this info is provided as is. It is up to you to determine if you want to use it and how you want to use it. In any way, test it before you implement it on a production box !