Event Manager Calendar View Contains Too Many Status Messages After Switching To SQL Server 2012

In SQL Server 2012, there seems to be some remnant debug code that is filling the Agent log with useless status messages. Please see the Connect post regarding this issue here:http://connect.microsoft.com/SQLServer/feedback/details/729700/. While in most cases this doesn't have a drastic effect on your server or its performance, you may notice that your Event Manager calendar is populated with a lot of informational messages.

Again, this isn't necessarily a bad thing, but in addition to crowding your calendar view, the underlying issue can cause performance issues when you are trying to read the log through various methods (e.g. xp_readerrorlog). The problem is that the SQL Server Agent Log gets larger and larger, even though it is mostly filled with noise. In a lot of cases, we've observed drastic performance impact when trying to read the log, so we actually check the log size first and don't process it if its size exceeds 1 MB. This is described in this post.

In Event Manager, you can automatically cycle the agent log when it hits 1 MB, by changing the SQL Server Connection setting "Auto-recycle large SQL Server Agent Logs" to true. In order to do this manually (for example, on servers where you're not using Event Manager), you can simply schedule a job that calls the following procedure periodically (which is what we do when we see that the log file has reached 1 MB):

EXEC msdb.dbo.sp_cycle_agent_errorlog;

This won't suppress the error messages from the log (or from the calendar view), but it will at least roll your log over so it doesn't grow unchecked (and so that we don't miss picking up events that are important). The workaround listed in the Connect item is to disable the Auto Registry Refresh feature. Using regedit.exe, go to the following key:

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server MSSQL11.INSTANCE_NAMESQLServerAgent

And change the value for “AutoRegistryRefresh” from 1 to 0.

Take note of the disclaimer, however: If you change any SQL Server Agent settings, you may need to restart the Agent service to see them.

Note that while you can suppress "Information" messages from the SQL Server Agent Log (right-click SQL Server Agent > Error Logs, choose "Configure", and uncheck "Information"), you should find that this is already unchecked by default, and that this specific information message is ignored by this setting in any case.

Have more questions? Submit a request

1 Comments

Please sign in to leave a comment.