December 6, 2010 at 8:42 pm
I am receiving the following error messages when a select is attempted from a view:
The operating system returned error 38(error not found) to SQL Server during a read at offset 0x000000d0c86000 in file 'K:\tempdev04.ndf'. 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.
Running checkdb on tempdb returns nothing suspect.
Interestingly, the offset does not change no matter how many times the query is run. This is still true even after I created a couple of huge temp tables specifically to see if the offset would change.
Based on the unchanging offset I wondered if perhaps it was a plan causing the problem. I ran dbcc freeproccache but the error still occurs.
I am able to select count(*) form the view, but not select *.
I am also able to select at least one named column. The query takes a long time to execute (up to 15 minutes) and there are many columns in the view.
I can think of two possibilities:
1) Tempdb is actually corrupt. I can fix with a restart of the SQL service, but this is a production system.
2) Somehow there is data that is getting into tempdb that looks like an end of file to the system (error 38 is an end of file reached). I would assume this is not as simple as an end of file character winding up as data in a varchar column somewhere, because that would surely be possibly legitimate data that the tabular data stream could cope with.
Any suggestions?
December 6, 2010 at 9:09 pm
One thing I forgot to mention: the database the view selects from is in snapshot isolation mode.
December 6, 2010 at 9:44 pm
Please check the System Event Log for any storage related errors. This has to do something with the underlying storage.
Like you mentioned, restarting the instance will re-create the tempdb. If this error persists even then, time to get in the Storage guy.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
December 6, 2010 at 9:55 pm
It could be a storage related issue, but there are no errors either in the eventlogs or in the checkdb output. I want restarting the sql service to recreate temdb (perhaps with a new location) to be my last option given that it means a temporary outage of the production ERP.
I also find it strange that it's always the same offset. After I created those big tables I would expect any objects in tempdb used by the query to be at different offsets.
December 6, 2010 at 10:03 pm
Even I have a database where this error comes in whenever a particular tables is queried. Like in your case, the storage related errors are no where logged. Even though everyone knows it is a storage related issue, due to lack of evidence the storage person keeps telling "everything is fine from storage perspective". Since this database will be decommissioned shrotly, I am not worried much about it.
I suggest you move tempdb to a new location and involve the storage guys to check the existing drive.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
December 6, 2010 at 10:13 pm
Cool, I'll point them to your post when they complain 😛
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply