March 1, 2010 at 5:39 am
"SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x22412a3d; actual: 0x2241223d). It occurred during a read of page (1:246058) in database ID 20 at offset 0x00000078254000 in file 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\<Insert Database Name>.mdf'
This seems to be a familiar error which is appearing on our databases, even though we run the dbcheck & repair_allow_data_loss commands.
Basically, one of our servers had a CPU problem, and we had to migrate to a newer x64 bit OS windows webserver 2008. We installed the same SQL server as we did on the old server (windows server 2003), backed up and transfered our databases to the new system. We restored the databases and began running queries to get everything back up to normal operations. However out of the 17 databases we have running, 6 of them began reporting the logical consistency-bases I/O error, we also noticed that it was only our larger databases that were returning the error in question.
After a week of searching the web for remedees, we finally was able to use the dbcheck and repair_allow_data_loss to repair the problem. However this turned out to be only a short term fix and the errors have since returned.
The databases in question hold data which is imported/updated each day. One table will likely be updated by a bulk insert from a flat file (static .txt/.csv file), while others get updated overnight through XML parsing and inserting into tables. These tables then get formatted/tossed around and finally settled in another table which houses the standardised data which we need.
This problem did not occur on our older server, and has only appeared on the new server, which is where i'm wondering how/where/why this error is appearing.
I've googled a fair bit and there doesn't seem to be much info out there as to why it's occuring, only that it is, and the only remedy is a restore or the only hope of repair_allow_data_loss.
Hope someone out there can identify our problem, and can advise a long term fix.
regards.
March 1, 2010 at 8:12 am
Gary T (3/1/2010)
"SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x22412a3d; actual: 0x2241223d). It occurred during a read of page (1:246058) in database ID 20 at offset 0x00000078254000 in file 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\<Insert Database Name>.mdf'
This seems to be a familiar error which is appearing on our databases, even though we run the dbcheck & repair_allow_data_loss commands.
Why are you happily repairing with allow data loss? Aren't you concerned about the data loss?
After a week of searching the web for remedees, we finally was able to use the dbcheck and repair_allow_data_loss to repair the problem. However this turned out to be only a short term fix and the errors have since returned.
Repair is a last resort for fixing corruption, used when there's no good backup to restore from. This may be worth a read. http://www.sqlservercentral.com/articles/65804/
This problem did not occur on our older server, and has only appeared on the new server, which is where i'm wondering how/where/why this error is appearing.
I've googled a fair bit and there doesn't seem to be much info out there as to why it's occuring, only that it is, and the only remedy is a restore or the only hope of repair_allow_data_loss.
Corruption is typically, the vast majority of the time, a problem with the I/O subsystem. Disks, controller (raid or SAN), cache, antivirus, other filter drivers, HBA drivers, firmware, etc.
I would suggest doing some serious diagnostics of that IO subsystem and, if possible, move the databases elsewhere. If the IO subsystem isn't trustworthy, it's not really a good idea to leave the databases on there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 1, 2010 at 9:22 am
GilaMonster (3/1/2010)
Gary T (3/1/2010)
"SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x22412a3d; actual: 0x2241223d). It occurred during a read of page (1:246058) in database ID 20 at offset 0x00000078254000 in file 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\<Insert Database Name>.mdf'
This seems to be a familiar error which is appearing on our databases, even though we run the dbcheck & repair_allow_data_loss commands.
Why are you happily repairing with allow data loss? Aren't you concerned about the data loss?
The data that is inserted isn't mission critical, however we'd rather avoid data loss if possible. However at the time this is the only method we found that could resolve the problem.
After a week of searching the web for remedees, we finally was able to use the dbcheck and repair_allow_data_loss to repair the problem. However this turned out to be only a short term fix and the errors have since returned.
Repair is a last resort for fixing corruption, used when there's no good backup to restore from. This may be worth a read. http://www.sqlservercentral.com/articles/65804/
Thanks for that link and your response, i'll be looking through it momentarily.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply