asked
My database went into suspected mode
My database went into suspected mode. and after we had run some script, it came out from the suspected mode. but we encountered this error while opening table in database. 2009-11-02 15:46:42.90 spid51 Error: 824, Severity: 24, State: 2. 2009-11-02 15:46:42.90 spid51 SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:43686; actual 0:0). It occurred during a read of page (1:43686) in database ID 23 at offset 0x0000001554c000 in file 'H:\MSSQL.SQL2008\MSSQL\DATA\my_db.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. What is the reason? How can I rectify this Problem?
First, run DBCC CHECKDB as it says. See if you're still suffering from corruption. Also, identify which object is corrupted. If it's a nonclustered index, the fix is easy, drop and recreate the index. If it's a table/clustered index, the best solution is to restore a good copy of the database from backup to point in time prior to the corruption. That assumes you're running backups and that you're running DBCC checks so you know which backup is good.
Which object is corrupted - you can find this using the undocumented `DBCC PAGE` command... At a rough guess, you'll need to enable trace flag 3604, and then run DBCC PAGE (23, 1, 43686). Check with google for some useful pages on how to do this, and how to interpret the results.
16 People are following this question.
