SQL Server 2005 SP1; infinite recompile during DBCC DBReindex
I found the following message in my error log last night:
A possible infinite recompile was detected for SQLHANDLE 0x02000000A2C80C28CD984E3FD44C2C7C9572357162F57191, PlanHandle 0x06000700A2C80C28B841E75C000000000000000000000000, starting offset 0, ending offset -1. The last recompile reason was 1.
I tried to find the sql query that went with the sql handle - but am not getting any results from either the Master, MSDB or database against which the job was run.
The error message showed up once a minute for an hour. The messages started about the time a maintenance job started. Based on some information I collect, looks like the job spent about 2 hours and 45 minutes reindexing one table (DBCC DBReindex).
This step which normally takes less than 5 minutes to run failed after running for 2 hours and 45 minutes. While this was happening, the transaction log for this database bloated from 7 to 30 GBs and maxed out the drive on which it was placed. Which then caused the maintenance job step to fail, transaction log to get filled up, etc.
Any ideas on what this error message means and what might have caused the subsequent events? I found something (http://www.kbalertz.com/Q814419/Infinite.Recompile.Occurs.Indexes.aspx) about indexes and infinite recompiles in the following site but the table that was reindexed for almost 3 hours last night is not a heap. And this was a hotfix for SQL Server 2000 SP2 - I'm on SQL Server 2005 SP1.
This table gets used by a web application to hold session data. So there might be millions of inserts, updates and deletes happening on a daily basis.
Any insight/advice will be much appreciated.
Smitha
