As a SQL Server administrator, you may have encountered situations where you need to launch jobs using alerts that are not owned by the standard SQL Server users. This is particularly true when using xp_cmdshell, which requires system administrator privileges. While SQL Server 2005 introduced SQLAgent proxies to address this issue, there are still scenarios where the alert system is necessary.

One common challenge with the alert system is the delay in SQLAgent’s response to events in the event log. By default, SQLAgent only checks for events every 20 seconds, which can result in slower response times. Unfortunately, there is limited information available in the official documentation regarding how to alter this behavior.

After extensive research and experimentation, I discovered a registry key called EventLogPeekInterval that controls the polling interval for SQLAgent. By modifying this key, we can significantly improve the response time of alerts. Here’s how you can do it:

-- 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

In the above code, you can set the @RegValue variable to any value between 10 and 3600 (in seconds). This will determine the polling interval for SQLAgent. Keep in mind that values lower than 10 seconds are not recommended, and if you require faster response times, you may want to consider using Service Broker instead.

It’s important to note that there are some restrictions imposed by the SQLAgent team. The value of the EventLogPeekInterval registry key must be greater than 9. If you set a value lower than 10, SQLAgent will ignore it and continue using the default 20-second interval. Additionally, if you set a value higher than 3600, SQLAgent will revert to the default interval as well.

Based on my testing, I recommend setting the EventLogPeekInterval to 10 seconds on SQL Servers where the alert system is in use. However, please remember that this information is provided “as is,” and it’s essential to thoroughly test any changes before implementing them on a production server.

Improving the response time of SQL Server alerts can significantly enhance the efficiency and effectiveness of your database management processes. By adjusting the EventLogPeekInterval registry key, you can ensure that critical events are detected and acted upon promptly.

Thank you for reading, and I hope this article helps you optimize your SQL Server alert system!