In the past 24 hours I have come across two bugs that can be quite problematic in certain environments.

LPIM issue with SetFileIoOverlappedRange

Last night the CSS team posted a blog entry detailing a potential issue with Lock Pages in Memory and Windows' SetFileIoOverlappedRange API. I tweeted about it at the time, but thought it could use a little more treatment. The potential symptoms can vary, but include the following (as quoted from the blog post):

Wide ranging in SQL from invalid write location, lost read or write, early access to a page that is not yet fully in memory, I/O list damage such as AVs, incorrect timing reports, and many others. You may not even see the situation until days later.

The fix for this will be issued through Windows Update and through SQL Server 2008 R2 Service Pack 2 (not yet released). In the meantime, which will hopefully be short, I recommend you disable Lock Pages in Memory to avoid these potential problems, if you are using SQL Server 2008 R2 or SQL Server 2012 *unless* you are already running on Windows 8.

SQL Server Agent Log bloat

In the SQL Server 2012 RTM release, there seems to have been some debug code left in that is filling the Agent log with useless status messages. In my case this is happening every 135 seconds or so, though the Connect item indicates it is every 90 seconds:

Now, this isn't a bad thing per se, but it is unnecessarily filling the log with these useless messages, and this can cause performance issues when you are trying to read the log through various methods (e.g. xp_readerrorlog). 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.

If you're using SQL Sentry 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:

However you will still see all of the events on your calendar, as we're currently not filtering these messages out.

If you're not using Event Manager, you might want to consider scheduling a job nightly or weekly that cycles the error log for you, since it will otherwise grow uninhibited as it fills up with these status messages. You can do this by simply calling the following stored procedure (which is exactly what we do behind the scenes when the log file hits the threshold):

EXEC msdb.dbo.sp_cycle_agent_errorlog;

This won't suppress the error messages from the log, but it will at least roll your log over so it doesn't grow unchecked. The workaround listed in the Connect item is to disable the Auto Registry Refresh feature. Using regedit.exe, go to the following key:

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

Note the disclaimer though: If you change any SQL Server Agent settings, you may need to restart the Agent to see them.