A possible infinite recompile was detected for SQLHANDLE
Marcello Miorelli (imported from SE) 
I have been finding strange error messages on the sql error log:

Bocss: same deadlock taking place every hour – needs investigating 

Also lots of recompiles are listed in the error log for other SPIDs as per following examples:

> 09/04/2015 14:30:10,spid64,Unknown,A possible infinite recompile was
> detected for SQLHANDLE
> 0x0200000059631A288882589E0C54B76404CAE1B97E08D3680000000000000000000000000000000000000000<c/>
> PlanHandle
> 0x0600040059631A2860A62B654100000001000000000000000000000000000000000000000000000000000000<c/>
> starting offset 1038<c/> ending offset 2600.  The last recompile
> reason was 2. 09/04/2015 14:30:10,spid150,Unknown,A possible infinite
> recompile was detected for SQLHANDLE
> 0x02000000EF886F018C4E0B163812B8B20150FE8FC7E6A06A0000000000000000000000000000000000000000<c/>
> PlanHandle
> 0x06000400EF886F01901A816E0600000001000000000000000000000000000000000000000000000000000000<c/>
> starting offset 998<c/> ending offset 2520.  The last recompile reason
> was 2. 09/04/2015 14:30:09,spid67,Unknown,A possible infinite
> recompile was detected for SQLHANDLE
> 0x0200000057C4C632D9052275CFF2B683B80F29501EE91D730000000000000000000000000000000000000000<c/>
> PlanHandle
> 0x0600040057C4C63200EAC2BE3000000001000000000000000000000000000000000000000000000000000000<c/>
> starting offset 1064<c/> ending offset 2652.  The last recompile
> reason was 2. 09/04/2015 14:30:09,spid163,Unknown,A possible infinite
> recompile was detected for SQLHANDLE
> 0x02000000E7C7BF0E5D70DE55759C7842860272AD474D69AB0000000000000000000000000000000000000000<c/>
> PlanHandle
> 0x06000400E7C7BF0EF0EB68A52C00000001000000000000000000000000000000000000000000000000000000<c/>
> starting offset 1028<c/> ending offset 2580.  The last recompile
> reason was 2.


what could have caused this?

Looks like I don't have the plans in cache anymore.
[![enter image description here][1]][1]


following the advice of this post 
[http://www.sqlservercentral.com/Forums/Topic1479420-146-1.aspx][2]

>  then as a safety measure disabled the full text catalogs, this made
> no difference, so then I fully rolled back the changes (dropped the
> new objects etc). This also made no difference, in the end the only
> thing that seemed to stop it was a restart of the SQL instances, this
> immediately resolved the issue.

this sorted me out as well, however, I am still to find what caused this mess at the first place?


  [1]: https://i.stack.imgur.com/MWf90.jpg
  [2]: http://www.sqlservercentral.com/Forums/Topic1479420-146-1.aspx
Top Answer
Paul White (imported from SE) 
According to [Infinite recompile message in the errorlog][1] on the SQL Programmability & API Development Team Blog, this message is triggered when a statement in a batch recompiles 100 times in a row.

This message does not necessarily mean there is a problem; it exists to help troubleshoot statements that might legitimately be recompiling that often (for example, due to rapid changes in statistics), as well as real infinite compilation loops (which would be rare in the extreme).

You should start by identifying the triggering statement from the information provided, and evaluate it in the context of the numeric code giving the reason for the recompilations. There is a table of these codes and their meanings in several places in Books Online, including under the [SP:Recompile Event Class][2].

There is more information available in [Plan Caching and Recompilation in SQL Server 2012](https://msdn.microsoft.com/en-us/library/dn148262.aspx).

[![Table of values][3]][3]


  [1]: http://web.archive.org/web/20160222013613/http://blogs.msdn.com/b/sqlprogrammability/archive/2006/04/21/580896.aspx
  [2]: https://msdn.microsoft.com/en-us/library/ms187105.aspx
  [3]: https://i.stack.imgur.com/5GTYm.png
comments
transcript

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.