Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server detected a logical consistency-based I/O error Expand / Collapse
Author
Message
Posted Monday, January 9, 2012 11:26 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:19 AM
Points: 1,044, Visits: 2,926
Hi,

Two months back Installed SQL server 2008 enterprise edition in PC based server.. Now database running with consistency error.. Could anyone suggestion me, how to resolve this error?

DBCC Checkdb 'dbname' with data_purity

Result

Msg 8928, Level 16, State 1, Line 1
Object ID 485576768, index ID 0, partition ID 72057594040745984, alloc unit ID 72057594041794560 (type In-row data): Page (1:425) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 485576768, index ID 0, partition ID 72057594040745984, alloc unit ID 72057594041794560 (type In-row data), page (1:425). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.

SQL Error Log and system Event Viewer

Message
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xbfc9c6fa; actual: 0x9fc9c6fa). It occurred during a read of page (1:425) in database ID 5 at offset 0x00000000352000 in file 'D:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\HWDATA7.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.

Thanks
ananda


  Post Attachments 
SQLDump0012.txt (19 views, 77.57 KB)
Post #1232926
Posted Monday, January 9, 2012 11:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, July 3, 2012 10:12 PM
Points: 62, Visits: 122
Data file can be repaired by DBCC repair command but there is risk of data loss so first take the database backup and also record all table count in excel file for compare the data after repair.

Step.1 Backup database.

Step.2 Get all tables record count.

Step.3 SET database in single user mode.

ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Step.4 RUN DBCC command-

DBCC CHECKDB(DB_NAME,REPAIR_ALLOW_DATA_LOSS)

Step.5 Get again all tables record count and compare with old record count.


SQL Query to get table record count-
DECLARE @T_Name VARCHAR(250)
DECLARE @COUNT INT
DECLARE @SQL VARCHAR(2000)
CREATE TABLE #T_Info(ID INT IDENTITY(1,1),T_Name VARCHAR(200),D_Count INT)

DECLARE TINFO_CUR CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
OPEN TINFO_CUR
FETCH NEXT FROM TINFO_CUR INTO @T_Name
WHILE @@FETCH_STATUS =0
BEGIN

SET @SQL='INSERT INTO #T_Info(T_Name,D_Count) SELECT '''+@T_Name+''',COUNT(*) FROM '+@T_Name+''


EXECUTE (@SQL)


FETCH NEXT FROM TINFO_CUR INTO @T_Name
END
CLOSE TINFO_CUR
DEALLOCATE TINFO_CUR


SELECT * FROM #T_Info ORDER BY T_NAME

DROP TABLE #T_Info


Post #1232933
Posted Tuesday, January 10, 2012 12:04 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:40 AM
Points: 7,001, Visits: 8,439
I would first look for things that may cause your kind of corruption:

A good starter is : http://sqlskills.com/BLOGS/PAUL/post/Example-20002005-corrupt-databases-and-some-more-info-on-backup-restore-page-checksums-and-IO-errors.aspx

You'll find more corruption/checksum related blogs on that site.


http://www.sqlservercentral.com/articles/Corruption/65804/ also contains very helpful information on your quest to solve your issue.



Only after having checked the options, I would try a "repair with data loss".


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1232937
Posted Tuesday, January 10, 2012 12:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:19 AM
Points: 1,044, Visits: 2,926
hi, Thanks for your reply and steps for sorted out. there is only one record was loss.
I am using DBCC CHECKTABLE ('Table_name', REPAIR_ALLOW_DATA_LOSS)

thanks
ananda
Post #1232939
Posted Tuesday, January 10, 2012 3:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
For future reference, repair should be the absolute last resort for corruption, no a knee-jerk first thing done.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1233011
Posted Tuesday, January 10, 2012 3:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:19 AM
Points: 1,044, Visits: 2,926
GilaMonster (1/10/2012)
For future reference, repair should be the absolute last resort for corruption, no a knee-jerk first thing done.


Hi Gail, what could be alternative solution to aviod data loss? when facing this type of error occurred in database.

I am facing first time this type of IO Error in CHECKDB. Eeven though not able to count all the records that particular table for due to 823 - a hard IO error.. So I am decided to ran DBCC CHECKTABLE ('Tablename', REPAIR_ALLOW_DATA_LOSS) command. after ran that those error was resolved.

thanks
ananda
Post #1233019
Posted Tuesday, January 10, 2012 4:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
CheckDB or CheckTable with repair allow data loss will lose data, that's why it's named that way (there are a couple cases where it won't, but they're rarer cases)

For recovery without data loss, take a tail-log backups and restore from backup, either full database, filegroup, file or page and then roll all the logs forward.

Take a look at this article. http://www.sqlservercentral.com/articles/65804/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1233046
Posted Monday, June 30, 2014 3:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:59 AM
Points: 1, Visits: 3
Thanx work perfect for me.
Post #1587532
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse