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

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

Ellis White 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...

My spouse and I absolutely love your blog and find the majority of your post's to be
what precisely I'm looking for. can you offer guest writers to write
content to suit your needs? I wouldn't mind writing a post or
elaborating on most of the subjects you write regarding here.
Again, awesome site!

Here is my site :: Need help with Magic Submitter - http://fitpro.co.za/members/armandoel/activity/19851/ -

Anonymous said...

Hi there terrific website! Does running a blog such as this
take a massive amount work? I have virtually no expertise in cding bbut I was hoping to start
my ownn blog in the near future. Anyway, should you have any suggestions or
tips for new blog owners please share. I understand this is offf topic however I simply wanted to ask.Kudos!


my hommepage :: minecraft premium account

Anonymous said...

Hey there just wanted to give you a quick heads up and let you know
a few of the pictures aren't loading properly. I'm not sure why but I think its
a linking issue. I've tried it in two different web browsers and both show the same results.



Feel free to visit my web-site - Termite Protection Windsor ON

Christopher Messner 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

Kaye R. Jenkins 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