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 PlanHandle 0x0600040059631A2860A62B654100000001000000000000000000000000000000000000000000000000000000 starting offset 1038 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 PlanHandle 0x06000400EF886F01901A816E0600000001000000000000000000000000000000000000000000000000000000 starting offset 998 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 PlanHandle 0x0600040057C4C63200EAC2BE3000000001000000000000000000000000000000000000000000000000000000 starting offset 1064 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 PlanHandle 0x06000400E7C7BF0EF0EB68A52C00000001000000000000000000000000000000000000000000000000000000 starting offset 1028 ending offset 2580. The last recompile reason was 2.
what could have caused this?
following the advice of this post http://www.sqlservercentral.com/Forums/Topic1479420-146-1.aspx
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?