DBCC CHECKDB consistency-based I/O error

Question

Running:

DBCC CHECKDB(DatabaseName) with NO_INFOMSGS 

gives me the following error:

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:7753115; actual 0:0). It occurred during a read of page (1:7753115) in database ID 11 at offset 0x00000ec9b36000 in file 'K:\UAT Databases\dbname.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.'

Also i found a entry in dbo.suspect_pages

Please advise.

Answer 1

1. Ensure you have a valid backup; hopefully it will be from prior to the corruption, but not so long ago that the data isn't useful. You should set this aside in case direct repair isn't possible and you need to recover data from the backup.

2. The documentation explains what to do to correct the problem - you can try the REPAIR_REBUILD option, and if that doesn't solve it, you can move on to next steps according to the guy who wrote CHECKDB, Paul Randal. He has a ton of articles on CHECKDB, that cover just about every conceivable scenario, but which ones are relevant to you will depend on what happens next when you attempt your repair. One that may be useful as a start, so you know which table you'll need to fix:

   • Finding a table name from a page ID

• Chad Mattox: In addition, 824 indicates a hardware error, so even if you are able to repair/restore, you need to resolve the hardware issue, or you will end up back in this situation.
• Aaron Bertrand: @Chad Yes, sorry, I assumed that was understood.
• Panagiotis Kanavos: @AaronBertrand incoming question about 2012 with no recent backup at stackoverflow.com/questions/78948560/…

Answer 2

Here's a great article on recovering a faulty db:

www.sqlservercentral.com/articles/Corruption/65804/

Firstly it advises things like: don't panic, don't restart the server, don't run a DBCC CHECKDB(DB_NAME, REPAIR_ALLOW_DATA_LOSS)

Then takes you through common fixes to common errors in the database, such as:
- Inaccurate space metadata
- Corruption only in the nonclustered indexes
- Corruption in the LOB pages
- Data Purity errors
- Corruption in the clustered index or heap
- Corruption in the Metadata
- Damaged system tables
- Damaged allocation pages

Answer 3

I've never had a corrupt page before. Can you provide any information from the dbo.suspect_pages table?

Please see the below resources:

Brent Ozar on Corruption

SQL Server Central Corruption

• user3688698: @Aurthur D result from suspect_pages table is database_id file_id page_id event_type error_count last_update_date 11 1 7753115 1 279 2016年05月25日 09:59:24.073

Answer 4

Run the following command to track the corrupt pages:

SELECT * FROM msdb.dbo.suspect_pages

Review the suspect pages.

Run DBCC CHECKDB command to check the inconsistencies of the database. Please check the PAGE_VERIFY CHECKSUM option and turn it on.

Note: Also check the hardware of your machine.