I have a SQL Server 2005 database that I backed up and restored onto a new server running SQL Server 2012. So far so good. This morning I had a really bad power outage and the new server went down. When the new server came back online the database had Recovery Pending status.
I put the database into single user mode, did a DBCC CHECKDB and then put the database back into multi user mode and the Recovery Pending status went away. When testing the backup jobs I noticed there was an issue with the full text catalog.
I tried to drop the catalog and recreate it, but it will not do it. It complains that it is not empty and that it has a full text index. I tried removing the index from the table that is using the full text catalog, but then I got a message saying the text catalog is empty, read only or offline.
What am I missing?
Note: The database is online, but the backup jobs keep failing. When I do a DBCC CHECKDB I get the following:
Msg 8996, Level 16, State 1, Line 1
IAM page (3:9) for object ID 983374968, index ID 1, partition ID 72057594124173312, alloc unit ID 72057594131513344 (type In-row data) controls pages in filegroup 0, that should be in filegroup 2.
Msg 2575, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (3:9) is pointed to by the next pointer of IAM page (0:0) in object ID 983374968, index ID 1, partition ID 72057594124173312, alloc unit ID 72057594131513344 (type In-row data), but it was not detected in the scan.
Msg 8968, Level 16, State 11, Line 1
Table error: IAM page (3:9) (object ID 983374968, index ID 1, partition ID 72057594124173312, alloc unit ID 72057594131513344 (type In-row data)) is out of the range of this database.
Msg 7965, Level 16, State 2, Line 1
Table error: Could not check object ID 983374968, index ID 1, partition ID 72057594124173312, alloc unit ID 72057594131513344 (type In-row data) due to invalid allocation (IAM) page(s).
CHECKDB found 2 allocation errors and 2 consistency errors in table 'sys.fulltext_avdl_1062555119' (object ID 983374968).
CHECKDB found 2 allocation errors and 2 consistency errors in database 'MY-DATABASE'.
Best Answer
Looking at your errors, especially 8996, looks like a hardware problem.
Run hardware diagnostics and correct any problems. Also examine the Microsoft Windows system and application logs and the SQL Server error log to see whether the error occurred as the result of hardware failure. Fix any hardware-related problems that are contained in the logs.
CHECKDB wont be able to repair this error. Best is to restore from a GOOD backup.
Refer here for more details.