SQL Server 2008 DBCC Problems

Question

We have a database Ms

DBCC CHECKDB (MS)

Or

ALTER DATABASE MS SET SINGLE_USER
DBCC CHECKDB(MS,REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE MS SET MULTI_USER

Error message

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

After spending hours and testing each table, the problematic table was found "LC"

DBCC CHECKTABLE(LC)

Error Message

Msg 211, Level 23, State 51, Line 1
Possible schema corruption. Run DBCC CHECKCATALOG.

Now,

DBCC CHECKCATALOG

Message

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I also tried to have a look on indexes

SELECT * FROM sysindexes

Error Message

Msg 211, Level 23, State 51, Line 1
Possible schema corruption. Run DBCC CHECKCATALOG.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

Again,

SELECT * FROM LC 

Or

DROP TABLE LC

Error Message

Msg 211, Level 23, State 51, Line 1
Possible schema corruption. Run DBCC CHECKCATALOG.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

I would not mind data loss for that particular table, I want to have the table dropped. Any ideas, please help!

Answer 1

Restore. Or you could RESTORE.

Oh, and never use REPAIR_ALLOW_DATA_LOSS (the article is worth reading)

Seriously: no amount of DBCC voodoo will bring your database back to a pristine, working state.

Answer 2

The most likely cause for these types of errors is a faulty disk drive. Before you continue troubleshooting, test the hard disk with hdtune or chkdsk /R. I'd also advise a memory test (there's a built in one called Windows Memory Diagnostic.)

Once you're sure the machine is OK, you could copy all good tables to a new database. Then you can drop the old database.