April 21, 2009 at 10:24 am
Hello all,
I have looked through out the forum but havn't found anything that covers my specific issue. I am getting error messages returned through email that tells me my database has a page torn issue or a bad page id but when i run dbcc checkdb doesn't return any issues. Any suggestions on what I could do to track down this issue. Below is a copy of my error message.
DATE/TIME:4/21/2009 9:03:28 AM
DESCRIPTION:Error: 823, Severity: 24, State: 2
I/O error (bad page ID) detected during read at offset 0x0000005c5ea000 in file 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\Ocon.mdf'.
COMMENT:(None)
JOB RUN:(None)
DATE/TIME:4/21/2009 9:00:27 AM
DESCRIPTION:Error: 823, Severity: 24, State: 2
I/O error (torn page) detected during read at offset 0x0000005c5e2000 in file 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\Ocon.mdf'.
COMMENT:(None)
JOB RUN:(None)
April 21, 2009 at 2:06 pm
Is there perhaps an index rebuild that runs after whatever triggered that mail but before the checkDB?
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
April 21, 2009 at 2:16 pm
Do u see any other errors in ur event viewer relating to your I/O problem?
May be there is problem in ur hardware or maybe device driver .
In ur event viewer logs, do u see write operations slow
or throwing errors?(Like write operation taking longer than n seconds)
April 21, 2009 at 2:25 pm
Gail,
I get these Emailed during the day and run the check db within 5 min of the error. So far today I have gotten 11 of these errors. But everytime after I get one the checkdb returns nothing
Thanks,
Jason
April 21, 2009 at 2:31 pm
Stupid question, and I know this may sound insulting (it's not the intention), are you sure you're running checkDB on the right database?
What version of SQL 2000? (SELECT @@Version)
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
April 21, 2009 at 2:35 pm
Mayank,
There is nothing in any event logs but the Torn page/ bad page id errors. I check sql, system, application and security and there is nothing besides the message I have already shown. One additional note this is running on a VM server, sorry I didn't mention that earlier.
April 21, 2009 at 2:35 pm
Gail,
I have run the check db in Query analyser with the database selected that is showing in the error message and it returns nothing I have also run DBCC CHECKDB ('Ocon' with NO_INFOMSGS) and got the same thing back.
My SQL Version is
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Thanks,
Jason
April 21, 2009 at 2:46 pm
How big is ur database? If u have taken full backups of this database, did u take on same drive? The reason I am asking is I had the same experience n error with no CHECKDB errors some time ago, and I was not able to take Full backup on that drive, the drive which was throwing I/O errors although I had not problems taking DIFF or LOG backups on the same drive. I also had problems in Restoration of my database on that drive but for small databases, it didnt show any errors.
I was able to take full backups of same Bigger database on other drive with no I/O error.
April 21, 2009 at 2:50 pm
I think I'm going to consult an expert on this one.
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
April 21, 2009 at 3:08 pm
Mayank,
The database is about 2 gig in size. We take full nightly backups of it both with backup exec and i run sql backups to a NAS device. We also run transaction log backups every 15 min to the same nas device. We don't ever back up any database to the same drive as the data is stored on. I have taken backups of the database and restored them to our test server and no errors are thrown on that database.
Thanks,
Jason
April 21, 2009 at 3:22 pm
Reviewing this article http://support.microsoft.com/kb/828339 tells us that we may not see errors using CHECKDB for an intermittent hardware problem.
Since you are getting these on a regular basis - you are (so far) lucky enough that it has not actually caused any corruption in your data file. However, it is just a matter of time until you actually see corruption in the data file and potential loss of data.
You need to get the hardware vendor on this ASAP to identify the specific hardware issues and get them resolved. The longer you wait - the more likely it is that you are going to end up with corruption in the data file and lose data.
I would make sure I had a good backup right now that I could restore from - because all indications are that you are going to suffer a hardware issue that is going to cause you lots of problems.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 21, 2009 at 6:04 pm
GilaMonster (4/21/2009)
I think I'm going to consult an expert on this one.
Gail asked me to chime in. There are two options here:
1) the page that was identified as corrupt is deallocated before you run DBCC CHECKDB, or it's being allocated as part of a transaction, which then tries to use the corrupt page it just allocated, fails, and rolls back the transaction, including the allocation of the page. Thus the page isn't allocated when DBCC CHECKDB runs, and won't be checked in any way.
2) it's some kind of stale read problem in a disk/RAID/SAN controller
Either way, it's your I/O subsystem that's causing the problem. Jeffrey's given you the steps to follow - this is definitely not a SQL Server problem.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply