A Possible Infinite Recompile was Detected - SQL Server Bug with Linked Servers

I recently assisted in migrating a very large system from SQL Server 2005 SP3 to SQL Server 2008 R2 SP1.  The actual migration of the database went off without a hitch, but we soon encountered an odd error with the application querying a view over a linked server.

The application was web-based and began throwing an error trying to load a view.  After some investigation, we discovered that the view was going across a linked server.  Every time the error was encountered, the Windows Event Viewer displayed the following error:

1A possible infinite recompile was detected for SQLHANDLE 0x020000004840002608194C0756C4E44307B50A91623589FD, PlanHandle 0x060012004840002640616630050000000000000000000000, starting offset 32, ending offset -1.  The last recompile reason was 6.

I took a look at the SQL Text using the supplied handle and matched it to what the web page was calling.  The recompile reason of type 6 turned out to be a problem with remote rows changing on the linked server.  We attempted different things to fix the issue, and thought maybe a maintenance run would assist.  In the end, restarting the main SQL Server instance (where the linked server was configured) resolved the issue.

The system ran great for a week, but then we encountered the same issue the next weekend.  After engaging Microsoft, we were pointed to a KB article about a bug with linked servers and synonyms across SQL Versions.

The article can be read here.

Cumulative updates are available for different versions of SQL Server to resolve the issue.  However, for those of you who cannot just take down a system to apply the patch, there is a temporary fix.

exec sp\_refreshview <view name>

On the main server that is executing the query across the linked server, the views pulling data over the linked server connection must be refreshed AFTER re-indexing or other schema-changing maintenance on the linked server.

We currently have a weekly job in place to refresh our relevant views until we can schedule the CU updates.  I only wish I had been able to find more information on this bug when I was experiencing it.  It would have saved us 8 hours in the middle of the night trying to figure it out.