SQL Server 2008 Enterprise on Windows Server 2008 Enterprise Cluster that is used to support SharePoint 2007 and 2010. We have a weekly index rebuild job that first checks the index fragmentation, and if there is greater than 10% fragmentation then the indexes are rebuilt, otherwise the index is left alone.
The job has been failing the past three runs with the only error being reported is:
Indexing in Database: SPSearchDBName [SQLSTATE 01000] ___________________________________ [SQLSTATE 01000] Re indexing for tables not needed in this Database. Fragmentation levels ok. [SQLSTATE 01000] Msg 208, Sev 16, State 1, Line 2 : Invalid object name '#work_to'. [SQLSTATE 42S02] Msg 0, Sev 16, State 1, Line 230 : [SQLSTATE 01000]
The invalid object name is just a fragment of the valid object name '#work_to_do'. How would the proc drop part of the temporary table name randomly? Some indexes are rebuilt successfully so the temp table works partially and it is not a specific database or table the job fails on. Any help on this is very much appreciated!