Error 823, Operating system error

  • 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.

  • 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

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

  • 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

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

  • 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

  • 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

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

  • 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

  • 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
  • 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?

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

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