Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'.
share|improve this question
    
Can you run DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS and post the output ? –  Kin Jun 28 '13 at 1:32
    
I get the same error –  redhatlab Jun 28 '13 at 1:57
    
Which recovery model is this database in? –  Jon Seigel Jun 28 '13 at 12:37
    
Recovery model is Full –  redhatlab Jun 28 '13 at 21:50
    
I would probably attempt a single-page restore if your chain of backups is in tact. –  Jon Seigel Jun 29 '13 at 1:44

1 Answer 1

up vote 0 down vote accepted

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.

share|improve this answer
    
Thank you for your response. I check the logs and it does not seems to be a hardware issue. The last GOOD backup is 2 days old. What else can I do? –  redhatlab Jun 28 '13 at 2:00
    
I noticed that the text full catalog in sys.fulltext_catalogs still show me a path, data_space_id and file id when it all should be null according to this mssqlfun.com/2012/09/21/… –  redhatlab Jun 28 '13 at 2:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.