Search This Blog

Loading...

Monday, March 28, 2011

SQL DBA - Corrupted DB ( logical consistency-based I/O error: torn page \ incorrect pageid \ incorrect checksum)

Errors :-

When u Run a Query you will get a Error like this :-

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0xd9706042). It occurred during a read of page (1:91308) in database ID 5 at offset 0x0000002c958000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Adb.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.

From Error Log you will find the Following Errors :-

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:91422; actual 20559:675107653). It occurred during a read of page (1:91422) in database ID 8 at offset 0x0000002ca3c000 in file 'H:\ITPS\PATDatabases\Adf.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.

2011-01-29 12:14:07.56 spid117 Error: 824, Severity: 24, State: 2.

2011-01-29 12:14:07.56 spid117 SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0x474447a6). It occurred during a read of page (1:91410) in database ID 8 at offset 0x0000002ca24000 in file 'H:\ITPS\PATDatabases\Adf.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.

2011-01-30 00:00:22.34 spid19s This instance of SQL Server has been using a process ID of 6132 since 1/29/2011 1:32:31 AM (local) 1/28/2011 8:02:31 PM (UTC). This is an informational message only; no user action is required.

2011-01-30 08:27:27.60 spid115 Error: 824, Severity: 24, State: 2.

2011-01-30 08:27:27.60 spid115 SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x67e10a73; actual: 0x5b34192a). It occurred during a read of page (1:91407) in database ID 8 at offset 0x0000002ca1e000 in file 'H:\ITPS\PATDatabases\Adf.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.

2011-01-31 00:00:52.72 spid15s This instance of SQL Server has been using a process ID of 6132 since 1/29/2011 1:32:31 AM (local) 1/28/2011 8:02:31 PM (UTC). This is an informational message only; no user action is required.

Solution For this Error :-

Torn Page Error -> it shows your Hardware Problems. Check your Hardware issues First.

Follow the Steps to Recover the Tables.

1. Check which Table is Corrupted using

DBCC Checktable ('Your_Table_Name’).

2. Goto SQL Server Managemant Studio

è Select the DB

è Right Click the DB

è Goto Properties

è Select Options

è Under Other Option select the Page Verify

è Select as NONE.

3. Run this Query to Change DB into Single User Mode

ALTER DATABASE corrupted_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE

4. Run this Query to Recover the Table/DB.

DBCC CheckTable ('corrupted_table', REPAIR_ALLOW_DATA_LOSS)

(OR)

DBCC CheckDB ('corrupted_db', REPAIR_ALLOW_DATA_LOSS)

5. Once this Execution Completed Sql returns “Errors are Corrected”

6. Run this Query to Change the DB into MultiUser Mode

ALTER DATABASE ApplicationManager SET MULTI_USER

7. 7. Goto SQL Server Managemant Studio

è Select the DB

è Right Click the DB

è Goto Properties

è Select Options

è Under Other Option select the Page Verify

è Select the Option CHECKSUM.

8. Now Run DBCC CHECKDB('Your DB')

No Error’s Will Show

4 comments:

Arattupuzha said...

Thanks
Its blog is very help ful to solve this problem
Anas

Anonymous said...

Awesome document, you are great.

Abhi said...

Suresh,
This was awesome! I was able to recover a really corrupted production database.

Thanks for the detailed tips to recover the pages.

Abhi

Adam Gorge said...

Msg 824 error get occurred when database get corrupted. In case DBCC command get failed to repair SQL Server database then you should use third party SQL recovery software. The software resolved all SQL server corruption errors. Stellar Phoenix SQL Recovery Software is one of the best tool & have more advanced features to repair corrupt SQL Server database.