Infinite recompile message in the errorlog
SQL Server 2005 sometimes outputs an infinite recompile warning to the errorlog. This blog post explains what this warning is about and what to do if you see it in the errorlog.
The new warning has the following format:
A possible infinite recompile was detected for SQLHANDLE <handle>, PlanHandle <handle>, starting offset <offset>, ending offset <offset>. The last recompile reason was <number>.
SQL Server 2005 prints this warning to the error log whenever a statement in the batch recompiles one hundred times in a row. The warning contains pertinent information such handle to the SQL text of the batch in which the recompile occurred, the handle to the corresponding compiled plan, the begin and end offset of the statement that recompiled (where by convention the end offset of -1 means the end of the batch), and the numeric code that identifies the reason for the recompile.
The warning message is produced because SQL Server assumes a large number of consecutive recompiles may be indicative of a problem. The purpose of the message is to reduce troubleshooting time for queries that appear to hang. If a query hits a true infinite recompile, it will never complete, and you will see the infinite recompile warning repeated indefinitely in the errorlog. The warning message allows you to easily identify the reason why the query never completes - it is because of the infinite recompile.
However, the message does not mean that there definitely is a problem; there are cases when the warning message legitimately appears in the errorlog. The most common recompile reason in the warning is 2 - the numeric code for statistics-based recompile. If statistics on a table accessed by a query change, a recompile for the query will be triggered, and if they change often enough, you could legitimately see this warning in the error log. Therefore, if you see the infinite recompile message appear in the errorlog with reason equal to 2, and you know the statistics on your tables are likely changing often (because the doing a lot of data modification), you can simply ignore the warning.
