Sql-server – SQL Server detected a logical consistency-based I/O error – tempdb

sql server

We started receiving these error messages suddenly

Error: 824, Severity: 24, State: 2.

Message SQL Server detected a logical consistency-based I/O error:
invalid protection option. It occurred during a read of page (7:8192)
in database ID 2 at offset 0x00000004000000 in file
'X:\tempdb\tempdb_mssql_6.ndf'. Additional messages in the SQL Server
error log or system event log may provide more detail. This is a
severe 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.

SQL Server detected a logical consistency-based I/O error: incorrect
checksum (expected: 0x31333637; actual: 0x9c88f38a). It occurred
during a read of page (7:8216) in database ID 2 at offset
0x00000004030000 in file 'X:tempdb\tempdb_mssql_6.ndf'. Additional
messages in the SQL Server error log or system event log may provide
more detail. This is a severe 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.

An error occurred while processing the log for database 'tempdb'. If
possible, restore from backup. If a backup is not available, it might
be necessary to rebuild the log. During undoing of a logged operation
in database 'tempdb', an error occurred at log record ID
(239:1702646:104). Typically, the specific failure is logged
previously as an error in the Windows Event Log service. Restore the
database or file from a backup, or repair the database.

SQL Server must shut down in order to recover a database (database ID
2). The database is either a user database that could not be shut down
or a system database. Restart SQL Server. If the database fails to
recover after another startup, repair or restore the database.


After these messages, I had to start SQL services manually, however the errors continued

Error while allocating extent for a worktable. Extent (6:16248) in
TEMPDB may have been lost.


I ran DBCC CHECKDB(tempdb) — no results…..

Checked with storage team for health checks, they say no issue.

However, I see many suspect_pages recorded for this DB and it even got recorded before the server crash too.

Suspect Pages

then I had to manually delete the tempdb files and restart SQL services. Errors stopped and DB health is OK.

We are on latest version – Microsoft SQL Server 2016 (SP2-CU8) (KB4505830) – 13.0.5426.0 (X64) Standard Edition (64-bit) …and I did this upgrade a week ago…it was running fine and suddenly it showed this behavior..and also Rebuild-Reorg maintenance job is running at that time…Storage admins denied any issues from their side…

Trying to investigate the root cause, any pointers/advise is really appreciable. Thanks in advance.

Best Answer

normally,824 means that there should be some hardware related issues ...Since tempdb is geting corrupt again and again , i would suggest you to involve hardware team\vendor and get your disks analyzed ...also check the application\system logs and see if there are disk related errors there...

  • Insufficient storage space on disk can cause error code 824
  • Hardware device or driver software which acts as a medium may cause errors
  • File system inconsistency can also be reason for SQL server logical consistency error
  • Damage caused to the database files may be a prime cause for the problem as well
  • Corruption of the file system can also give rise to SQL fatal error 824

Use the SQLIOSim utility to find out if these 824 errors can be reproduced outside of regular SQL Server I/O requests.