gtrapp
asked on
How to fix SQL Server detected a logical consistency-based I/O errors?
Hello,
After running this command and seeing this error in the systems logs and SQL Server logs, I see this error.
Command:
DBCC CHECKDB (wss_pbc_content_hermes) WITH ALL_ERRORMSGS, NO_INFOMSGS
Error:
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:57481; actual 0:0). It occurred during a read of page (1:57481) in database ID 26 at offset 0x0000001c112000 in file 'D:\SP_Databases\MSSQL10_5 0.MSSQLSER VER\MSSQL\ DATA\wss_p bc_content _hermes.md f:MSSQL_DB CC26'. 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.
Is there any hope to repair this type of error? What can I do?
Thanks.
After running this command and seeing this error in the systems logs and SQL Server logs, I see this error.
Command:
DBCC CHECKDB (wss_pbc_content_hermes) WITH ALL_ERRORMSGS, NO_INFOMSGS
Error:
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:57481; actual 0:0). It occurred during a read of page (1:57481) in database ID 26 at offset 0x0000001c112000 in file 'D:\SP_Databases\MSSQL10_5
Is there any hope to repair this type of error? What can I do?
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I ran the DBCC CHECKDB, it reported 0 errors and repaired 0.
DBCC CHECKDB (wss_pbc_content_hermes) WITH no_infomsgs executed by PBTF\SvcSQL found 0 errors and repaired 0 errors. Elapsed time: 0 hours 5 minutes 38 seconds. Internal database snapshot has split point LSN = 0002e195:000000d8:0001 and first LSN = 0002e195:000000d7:0001.
Not sure where to find the problems and why 823 and 824 are being generated.
DBCC CHECKDB (wss_pbc_content_hermes) WITH no_infomsgs executed by PBTF\SvcSQL found 0 errors and repaired 0 errors. Elapsed time: 0 hours 5 minutes 38 seconds. Internal database snapshot has split point LSN = 0002e195:000000d8:0001 and first LSN = 0002e195:000000d7:0001.
Not sure where to find the problems and why 823 and 824 are being generated.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I ran the simulation on the database server with SQLIOSim and have this log file. I am not experienced enough to interpret the results. Can you help?
********** Final Summary for file d:\test\log1\sqliosim.mdx **********
File Attributes: Compression = No, Encryption = No, Sparse = No
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 97, Number of times IO throttled = 0, IO request blocks = 483
Reads = 0, Scatter Reads = 0, Writes = 0, Gather Writes = 11038, Total IO Time (ms) = 2830768
DRIVE LEVEL: Sector size = 512, Cylinders = 76489, Media type = 12, Sectors per track = 63, Tracks per Cylinders = 255
DRIVE LEVEL: Read cache enabled = No, Write cache enabled = No
DRIVE LEVEL: Read count = 393, Read time = 3358, Write count = 59517, Write time = 20524094, Idle time = 14797, Bytes read = 4298752, Bytes written = 17423657472, Split IO Count = 4, Storage number = 3, Storage manager name = VOLMGR
Closing file D:\test\log2\sqlioim.mdx
********** Final Summary for file D:\test\log2\sqlioim.mdx **********
File Attributes: Compression = No, Encryption = No, Sparse = No
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 1159, Number of times IO throttled = 0, IO request blocks = 494
Reads = 0, Scatter Reads = 0, Writes = 0, Gather Writes = 20480, Total IO Time (ms) = 7103677
DRIVE LEVEL: Sector size = 512, Cylinders = 76489, Media type = 12, Sectors per track = 63, Tracks per Cylinders = 255
DRIVE LEVEL: Read cache enabled = No, Write cache enabled = No
DRIVE LEVEL: Read count = 398, Read time = 3518, Write count = 82833, Write time = 21078919, Idle time = 17768, Bytes read = 4343808, Bytes written = 29023797248, Split IO Count = 12, Storage number = 3, Storage manager name = VOLMGR
Closing file d:\test\log3\sqliosim.mdx
********** Final Summary for file d:\test\log1\sqliosim.mdx **********
File Attributes: Compression = No, Encryption = No, Sparse = No
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 97, Number of times IO throttled = 0, IO request blocks = 483
Reads = 0, Scatter Reads = 0, Writes = 0, Gather Writes = 11038, Total IO Time (ms) = 2830768
DRIVE LEVEL: Sector size = 512, Cylinders = 76489, Media type = 12, Sectors per track = 63, Tracks per Cylinders = 255
DRIVE LEVEL: Read cache enabled = No, Write cache enabled = No
DRIVE LEVEL: Read count = 393, Read time = 3358, Write count = 59517, Write time = 20524094, Idle time = 14797, Bytes read = 4298752, Bytes written = 17423657472, Split IO Count = 4, Storage number = 3, Storage manager name = VOLMGR
Closing file D:\test\log2\sqlioim.mdx
********** Final Summary for file D:\test\log2\sqlioim.mdx **********
File Attributes: Compression = No, Encryption = No, Sparse = No
Target IO Duration (ms) = 100, Running Average IO Duration (ms) = 1159, Number of times IO throttled = 0, IO request blocks = 494
Reads = 0, Scatter Reads = 0, Writes = 0, Gather Writes = 20480, Total IO Time (ms) = 7103677
DRIVE LEVEL: Sector size = 512, Cylinders = 76489, Media type = 12, Sectors per track = 63, Tracks per Cylinders = 255
DRIVE LEVEL: Read cache enabled = No, Write cache enabled = No
DRIVE LEVEL: Read count = 398, Read time = 3518, Write count = 82833, Write time = 21078919, Idle time = 17768, Bytes read = 4343808, Bytes written = 29023797248, Split IO Count = 12, Storage number = 3, Storage manager name = VOLMGR
Closing file d:\test\log3\sqliosim.mdx
ASKER
I may have found something that could fix the problem:
http://blogs.msdn.com/b/psssql/archive/2010/01/08/dbcc-check-commands-encounter-problems-and-reports-errors.aspx
http://blogs.msdn.com/b/psssql/archive/2010/01/08/dbcc-check-commands-encounter-problems-and-reports-errors.aspx
You still need to have someone verify the integrity of the disks. If that's bad, of cousre nothing else you do will matter, so you have to resolve the potential disk integrity issue as well.
ASKER
After installing the patch for Diskeeper, I have not seen 823 and 824 in the log files, and DBCC CHECKDB reports no consistency and integrity errors with the databases. Backups are being generated successfully, as well. I did not check the disks, but the server is not reporting any other problems at the moment. It's a brand new server with Windows Server 2008 R2.
The error logical consistency means that, the requested page has been read successfully from the disk by the user, but the page is troubling. You might also face ‘fatal error’ in the SQL Server error log or the Windows application event log.
You can go this blog which I had written. Here I have discussed that how to fix the issue logical consistency based I/O error.
http://sqltechtips.blogspot.in/2015/11/troubleshooting-error-824.html
You can go this blog which I had written. Here I have discussed that how to fix the issue logical consistency based I/O error.
http://sqltechtips.blogspot.in/2015/11/troubleshooting-error-824.html
ASKER
I looked at first time of the errors occured and did not see additional information. Are you talking about the Windows Log or SQL Server Logs. Both say the same thing.
I did not find any records in the suspect_pages table in the msdb table.
This is event ID 823:
The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x0000001c112000 in file 'D:\SP_Databases\MSSQL10_5