December 23, 2009 at 1:21 pm
When I run CHECKDB on a user database the windows event log reports 2 related events...
1).The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x000000e9f38000 in file 'A:\aaaaaa\bbbb\ccccccccc.MDF:MSSQL_DBCC7'.
2).DBCC CHECKDB (yyyyyyyyy) WITH no_infomsgs executed by xxxxxxxxxxxxxxxxx found 0 errors and repaired 0 errors. Elapsed time: 0 hours 16 minutes 42 seconds.
I'm assuming this suggests we have a hardware (disk) related issue affecting the primary (MDF) data file???
Are there any suggestions with regards to next steps for further diagnosing the issue?
Thanks in advance.
Chris.
December 23, 2009 at 1:36 pm
The end of file message refers to the hidden snapshot that CheckDB takes. It's not affecting the mdf file itself (if it was, CheckDB would find a lot more than 0 errors) but it's still an IO problem.
I suggest you do some IO diagnostics, look in the windows event log (system) an check any logs that the IO system may itself have.
What's the full and compete output of this? (you can mangle the DB names, just leave the messages intact)
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
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
December 23, 2009 at 2:07 pm
First, thanks for the reply.
Unfortunately I don't see anything helpful in the Windows system event log. I've pasted the full content of the pertinent windows (Application) event log entries below. It could be completely unrelated but a vendor recently installed an online disk defrag tool on our RAID device where this particular database lives.
The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x000000e9f38000 in file 'X:\AAAA\BBBB\CCCCCCCCC.MDF:MSSQL_DBCC7'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level 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.
DBCC CHECKDB (aaaaaaaaa) WITH no_infomsgs executed by aaaaaaaaa\bbbbbbbbbb found 0 errors and repaired 0 errors. Elapsed time: 0 hours 16 minutes 42 seconds.
The SQL Server error logs contain the same information with one additional message
Error: 823, Severity: 24, State: 2.
December 23, 2009 at 2:21 pm
chris.worthington (12/23/2009)
It could be completely unrelated but a vendor recently installed an online disk defrag tool on our RAID device where this particular database lives.
Uninstall it or disable it. Then get the vendor to confirm whether or not the defrag tool works properly with SQL server, alternate streams and sparse files. Some statement from MS would be the best. Once it's uninstalled/disabled, try the checkDB again. If it succeeds then you've virtually got proof that the defrag tool's the problem.
I'd guess that it's treating the DB just like any other file and that could easily cause the error that you've got. If the defrag tool moves a part of the file around while SQL's accessing it things could get messy. The error indicates that the end of file was encountered unexpectedly. ie the file's not as long as the OS says it is, or the end of file wasn't where it's supposed to be.
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
December 23, 2009 at 2:37 pm
Thanks for the suggestion/advice. I'll disable the defrag tool on that volume and try the CHECKDB command again.
Regards,
Chris.
December 29, 2009 at 2:53 pm
Just a quick update....after disabling the disk defrag tool the DBCC CHECKDB command was successful. So it looks like we have identified the culprit.
Thanks for your help.
Chris.
December 30, 2009 at 1:27 am
Glad to hear that.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply