Error 823, Operating system error

Question

Error 823, Operating system error

Denise McMillan

SSCertifiable

Points: 6216
More actions
March 27, 2009 at 3:40 pm

#219478

We have the following error:
Error: 823, Severity: 24, State: 7.
Operating system error 'incorrect pageid (expected 1:216; actual 0:477691182)' resulted from attempt to read the following: sort run page (1:216), in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf', in database with ID 2. Sort is retrying the read.
DESCRIPTION: The operating system returned error stale page (a page read returned a log sequence number (LSN) (47:398:55) that is older than the last one that was written (0:0:0)) to SQL Server during a read at offset 0x00000000290000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf'. 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.
I ran dbcc checkdb on all databases with no errors. Our Server Administrator doesn't see any errors.
Any ideas what else we should look for?
Thanks very much.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

It's a tempdb page. You could try running CHECKDB on tempdb, but the checks are somewhat limited on tempdb, and it's likely the page isn't allocated any more and so wouldn't be read by CHECKDB anyway.

You've got IO subsystem problems on whatever you're storing tempdb on - a controller returned an old copy of a page rather than the most recent one. You need to run IO subsystem diagnostics, possibly SQLIOSim. Unfortunately page checksums aren't there on tempdb until SQL 2008.

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

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 2

Paul does a restart of SQL, and a restart of tempdb clear this up?

Paul Randal One Orange Chip Points: 29544 More actions · Answer 3

Yes and no. Yes, in that tempdb will be recreated and may use a different part of the drives hosted by the IO subsystem. No, because your IO subsystem still has something wrong with it and most likely will fail again. Now, if tempdb is on the same IO subsystem as other databases, next time it could be corruption in one of your user databases...

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

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 4

Thanks very much for your response. We'll run the IO diagnostics.

abishekshroff SSCrazy Points: 2040 More actions · Answer 5

Trace flag 818 may help to find exact reason. I can bet issue is because of I/O subsystem.

Check the I/O subsystem

Enable TF 818 to find more information if it occurs in future

Paul Randal One Orange Chip Points: 29544 More actions · Answer 6

It would help if you explained what the trace flag does... 818 enables some code which could help with the detection of stale reads. You could also try trace flag 806, which will do page auditing whenever a page is read from disk. This can be useful when page checksums aren't enabled (i.e. tempdb in SQL 2005 where page checksums cannot be turned on).

Thanks

PS Beware that enabling these in production will slow down your workload - possibly quite a bit.

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

Denise McMillan SSCertifiable Points: 6216 More actions · Answer 7

Thanks for the clarification. I will look into these traces too.

francis.nicholas SSC Enthusiast Points: 133 More actions · Answer 8

Any luck with this issue? I am experiencing one has of this writing on SQL2008. Were you able to successfully troubleshoot it?

I look forward to hearing from you.

Thanks,

Francis

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Please post new questions in a new thread. Thank you.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

francis.nicholas SSC Enthusiast Points: 133 More actions · Answer 10

I dont understand and I dont think this is a new question (correct me if I am wrong). It is relating to the issue on this thread. . in fact, I am experiencing the same error?