Generally 823 error message occurs when:
•A ReadFile, WriteFile, ReadFileScatter, WriteFileGather, or etOverlappedResult operation results in any operating system error code.•The page number on the page read from disk is not the expected page ID.•The data transfer size is not valid.•A torn read is detected when torn page detection is enabled.•A stale read is detected when stale read detection is enabled.
A ReadFile, WriteFile, ReadFileScatter, WriteFileGather, or etOverlappedResult operation results in any operating system error code.
The page number on the page read from disk is not the expected page ID.
The data transfer size is not valid.
A torn read is detected when torn page detection is enabled.
A stale read is detected when stale read detection is enabled.
SQL Server 2000 uses standard Windows API calls, such as ReadFile, ReadFileScatter, WriteFile, and WriteFileGather, to perform I/O with its database files. When SQL Server 2000 uses the Windows API calls, the file has already been opened successfully, or SQL Server 2000 would not try to read from it or write to it. Therefore, if a Windows API call is not successful and if the error is anything other than the operating system error 6 ("Invalid Handle"), the error is likely being raised in Windows or by a lower-level software component, such as a device driver. Because the operating system error 6 is an invalid handle, the problem may occur if SQL Server is using an invalid handle to make a Windows API call. However, this may still be a system problem.
Steps you should take to troubleshoot is that :
1. Please check your hardware with your hardware vendore.
2. Run SQLIOSTRESS utility. You can use this utility to perform stress tests on disk subsystems to simulate Microsoft SQL Server 2000 and Microsoft SQL Server 7.0 read, write, checkpoint, backup, sort, and read ahead activities.
3. Enable the trace flag 818 as SQL startup parameter.
Trace flag 818 enables an in-memory ring buffer that is used for tracking the last 2,048 successful write operations that are performed by the computer running SQL Server, not including sort and workfile I/Os. When errors such as Error 605, 823, or 3448 occur, the incoming buffer's log sequence number (LSN) value is compared to the recent write list. If the LSN that is retrieved during the read operation is older than the one specified during the write operation, a new error message is logged in the SQL Server error log. Most SQL Server write operations occur as checkpoints or as lazy writes. A lazy write is a background task that uses asynchronous I/O. The implementation of the ring buffer is lightweight, thereby making the performance affect on the system negligible.
I still recomend you to check the hardware of the system and update to latest firmaware. I have seen in many cases that the hardware vendore will report that there is no problem with the hardware. In that case plaese run the SQLIOSTRESS and check the output which reports for stale read / lost write.
Lost Write: A successful call to the WriteFile API, but the operating system, a driver, or the caching controller does not correctly flush the data to the physical media even though SQL Server is informed that the write was successful.
Stale Read: A successful call to the ReadFile API, but the operating system, a driver, or the caching controller incorrectly returns an older version of the data.
Links for your reference:
email id : email@example.com