4

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.

Improve this question
3
  • Are there any errors in the Windows application log (on the SQL Server Server) around the time this occurred ?
    – armitage
    Commented Sep 30, 2019 at 21:09
  • Most corruption issues creep in from driver bugs. I usually describe this as software errors between Windows and the physical disks. If you're lucky, you'll find something in the event logs, but I'd also start by looking for available driver updates and reviewing associated release notes.
    – AMtwo
    Commented Sep 30, 2019 at 21:35
  • Windows app log has similar errors as i mentioned above... 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....nothing more
    – Kris
    Commented Oct 1, 2019 at 14:21

1 Answer 1

Reset to default
3

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.

Improve this answer
8
  • Thanks for your inputs....and RebuildOptimize Index job started running at 9PM on 09/27 and server crashed at 10:15PM....i check DPA monitoring tool and see no Mem/CPU/Disk issues....also...SQL agent did not record anything about the job failure due to server crash..also SQL services were set automatic, inspite of that...they did not com online and i had to manually start them....where else i can check more details....raising a MS case would help more?
    – Kris
    Commented Oct 1, 2019 at 15:54
  • Is this a virtual mashine? vmware or hyper-v.. please send an output of the following command in t-sql: xp_readerrorlog
    – Benedikt Schackenberg
    Commented Oct 1, 2019 at 18:25
  • this is a physical machine System Manufacturer: Dell Inc. System Model: PowerEdge R740 System Type: x64-based PC Processor(s): 1 Processor(s) Installed. [01]: Intel64 Family 6 Model 85 Stepping 4 GenuineIntel ~2095 Mhz Storage Type is PURE due to sensitivity of the work i support here, i can not provide complete error log details. Please let me know the details you are looking for...
    – Kris
    Commented Oct 1, 2019 at 19:54
  • Check your filesystem where tempdb is located (chkdsk) and take a look in your errorlogs.
    – Benedikt Schackenberg
    Commented Oct 2, 2019 at 9:07
  • Yes, i checked and as mentioned there were many logical consistency-based I/O errors recorded for the tempdbfiles on the dedicated drive for tempdb database...this drive holds only tempdb data and log files.....i have created 8 data files and 1 log file with unlimited growth....there are no signs of any drive full alerts in error logs....
    – Kris
    Commented Oct 2, 2019 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.