March 21, 2012 | SQL Server

Two bugs you should be aware of

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:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.INSTANCE_NAME\SQLServerAgent

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.

5 comments on this post

    • techvslife - March 21, 2012, 10:03 PM

      Thanks Aaron.  I think I may have hit this locked pages bug for the first time Sunday, on win 7 sp1 x64 and sql 2012 rc0 x64.  It showed as a file io freeze on initial open of templog.ldf after sql server service was restarted (in event log viewer).  
      According to the blog entry you cite, it wouldn't affect RTM of sql 2012:  "This is a race condition trigged when using SQL 2008 R2 and (Denali (SQL 2012) *before RC1*)."  –But better safe than sorry until the win7 patch.

    • Aaron Bertrand - March 21, 2012, 10:26 PM

      That was my thinking too – play it safe until you're patched.

    • Parikshit Savjani - March 30, 2012, 5:49 PM

      Hi Aaron,
      Just to add to your list. There is one more critical bug wherein if you define identity column on a table and insert some values. Thereafter restart SQL server and insert further more values and do a restart again and then when u insert in that table there are large gaps in the identity values.
      Thought it might be worth to append to your list to inform the end users.

    • Ian Yates - April 11, 2012, 3:00 AM

      I think the Windows patch just got released – http://support.microsoft.com/kb/2679255.  It was available in my Auto Updates for my Win 7 machine today (2012-04-11 Australia).

    • TechVsLife - April 15, 2012, 9:03 AM

      Patch was released for windows–I'm not sure but it appears that one also has to turn the trace flag 8903 on in sql server 2012 rtm to get locked pages functionality again.  (trace flag mentioned in original report):
      http://blogs.msdn.com/b/psssql/archive/2012/03/20/setfileiooverlappedrange-can-lead-to-unexpected-behavior-for-sql-server-2008-r2-or-sql-server-2012-denali.aspx

Comments are closed.