SQL Server detected a logical consistency-based I/

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

SQL Server detected a logical consistency-based I/

Author

Topic

mandeep2k9
Starting Member

1 Post

Posted - 2009-06-22 : 05:10:36

Hi,
i am using sql server 2005 service pack 2. and one of my database is come in Suspect state (B'coz of uncertianly shut down of my system). then i repair the database by using the Following Querries. which are as follows:.

EXEC sp_resetstatus ‘yourDBname’;

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb(’yourDBname’)

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (’yourDBname’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

# Dbcc Checkdb is not completed Successfully and it will throw the exception as follows:-

DBCC results for 'LOGICDBRIC'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'LOGICDBRIC'.
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: 0x5555aaaa). It occurred during a read of page (1:1701015) in
database ID 8 at offset 0x0000033e92e000 in file 'h:\logic\ric\LOGICDBRIC_Data.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.

* but when i changed the my database to Multi User it come in Normal state but i am not able to fetch my data properly......

So, Pls Guide me how i can repair this problem and get my data properly

Regards,
Mandeep

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-06-22 : 06:27:05

Can you please run this and post the entire output.

DBCC CHECKDB (< Database Name > ) WITH NO_INFOMSGS, ALL_ERRORMSGS

Do you have a backup of this database?

--
Gail Shaw
SQL Server MVP

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2009-06-23 : 10:33:09

If EMERGENCY-mode repair could not repair the database fully then you have a few choices:

1) try to manually drop the broken table
2) extract out as much info into a new database as possible
3) restore from a backup

Here's a trick to try - turn OFF page checksums and then try to extract out info in to a new database.

Btw - a page checksum failure is solid proof that your I/O subsystem is causing corruption.

Paul S. Randal,
Managing Director, SQLskills.com
Blog: www.SQLskills.com/blogs/paul Twitter: twitter.com/PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code

Davidpoul
Starting Member

14 Posts

Posted - 2009-07-14 : 05:26:17

To get your data back in proper manner you need to repair your data with the help of sql recovery software. These software uses powerful algorithm to scan data and repair them. You can download it from http://www.mssqldatabaserecovery.com

David Poul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources