Search This Blog

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

16 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

Unknown 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.

Unknown said...

Please read this blog, it also explain that how to fix this error. http://www.sqlrecoverysoftware.net/blog/sql-server-page-level-corruption.html

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...
This comment has been removed by a blog administrator.
Unknown said...

SQL database Recovery is the perfect solution to recover all corrupt or damaged MS SQL Server database files accurately. The SQL Server database files may get corrupt due to several reasons that is, virus attacks, abnormal system shut down, network issues etc. This tool efficiently recovers entire MDF files including triggers, tables, keys, procedures, indexes. It automatically rebuilds a new database MDF file making it the best tool among many others in the market.
The demo version of tool is available to download for free from:- http://www.undeletepcfiles.com/sql-database-recovery-tool.html

Unknown said...

SQL database Recovery Software remove errors from SQL server database and allow recover your all database of SQL Server 2012 or below versions. The software support with all over version of SQL server. Try at here:- http://www.recoverydeletedfiles.com/sql-database-recovery-software.html

Unknown said...

If you want repair you SQL server database and recover also from corrupt and damaged SQL server, many type of reason of corruption of SQL Server database but you don’t worry about that try this :-
http://www.pcrecoveryutility.com/sql-database-recovery.html

Unknown said...

Here is the other one that repairs corrupt SQL server data files. http://www.recoverfilesdownload.com/sql-database-recovery.html

Anonymous said...

Nicely written article after a long googling I also found this article here also have discussed in very easy words. http://sqltechtips.blogspot.in/2015/11/troubleshooting-error-824.html

Rajesh said...

Thanks,this is very helpful.

Unknown said...

There are many reasons behind SQL server error 824, you can take the help of below link to know the reason of this error and methods to fix SQL error 824: https://sqltechtips.blogspot.com/2015/11/troubleshooting-error-824.html

Suresh Thiravidamani said...

Hi Suresh,
Thanks your solution. I have resolved same issue in production db with your help.
Thanks so much....

Anonymous said...

Thanks suresh