SQL Server Error : 824, Severity: 24. SQL Server detected a logical consistenc

SQL Server Error : 824 Details

SQL Server Error: 824
Severity: 24
Event Logged or not: Yes
Description:
SQL Server detected a logical consistency-based I/O error: %ls. It occurred during a %S_MSG of page %S_PGID in database ID %d at offset %#016I64x in file ‘%ls’. 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.
Severity 24 Description:
Indicates a media failure. The system administrator may have to restore the database. You may also have to call your hardware vendor.

 

Reasons for this error:

This error indicates that while Windows reports that the page was successfully read from disc, SQL Server discovered a problem with the page. This error is similar to error 823, except that Windows did not detect it. It typically indicates a problem in the I/O subsystem, such as a failing disc drive, disc firmware issues, a faulty device driver, and so on. See Microsoft SQL Server I/O Basics for more information on I/O errors.
SQL Server performs I/O operations using Windows APIs such as ReadFile, WriteFile, ReadFileScatter, and WriteFileGather. SQL Server checks for any error conditions associated with these API calls after performing these I/O operations.

The 824 error contains the following information:

• The page number that was involved in the I/O operation
• Was the operation a read or write operation
• The database file against which the I/O operation is performed
• The offset with the file where the I/O operation was attempted
• The database to which this file belongs
• Details about the logical consistency check that failed [The type of check, actual value and expected value used for this check]

These logical consistency checks are additional integrity checks performed by SQL Server to ensure that certain key aspects of the data involved in the data transfer were preserved throughout the I/O operation. Checksum, Torn Page, Short Transfer, Bad Page ID, Stale Read, and Page Audit Failure are among the checks. The nature of the checks performed varies depending on the database and server configuration options.

Reading sql server error log location from SQL Query

Identifying SQL Server Error Log File used by SQL Server Database Engine can be done by reading SQL Server Error Logs. DBA can execute the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log and search for its location used by the instance of SQL Server.

USE master
Go
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
Go

The parameters for XP_READERRRORLOG are:
1. Value of error log file we would like to read. values are 0 = current, 1 = last one before current, 2 = second last before current etc…
2. Log file type:- 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1:- String one you want to search for
4. Search string 2:- String two you want to search for to further refine the results
5. start time for Search
6. end time for search
7. Sort order for search results:- N’asc’ = ascending, N’desc’ = descending

By default, we have 6 Server Error Logs kept but we can increase the number of SQL Server Error Logs from the default value of six.

For other ways to read and find error log location please our artcile https://sqlserver-dba.co.uk/error-log/sql-server-identify-location-of-the-sql-server-error-log-file.html

Solution for Resolving the Error

1.Examine the suspect pages table in msdb to see if other pages [in the same or different databases] are experiencing this issue.
Using the DBCC CHECKDB command, verify the consistency of the databases in the same volume [as reported in the 824 message]. If you discover inconsistencies after running the DBCC CHECKDB command, follow the instructions in the Knowledge Base article How to troubleshoot database consistency errors reported by DBCC CHECKDB.
If the database that generates these 824 errors does not have the PAGE VERIFY CHECKSUM database option enabled, it should be done right away. Other than a checksum failure, 824 errors can occur, but CHECKSUM is the best option.

2. Examine the Windows Event logs for any errors or messages from the operating system, a storage device, or a device driver. Please address those errors first if they are related to this error in any way. In addition to the 824 message, you may notice an event in the Event Log such as “The driver detected a controller error on DeviceHarddisk4DR4” reported by the Disk source. In that case, you must first determine whether this file is present on this device and then correct any disc errors.

3.Use any diagnostic utilities provided by the hardware vendor or device manufacturer to assess the health of the I/O system.
Check to see if there are any Filter Drivers in the path of these problematic I/O requests.
Check to see if these filter drivers have been updated.
Can these filter drivers be removed or disabled to see if the issue that causes the 824 error resolves?
If the issue is not hardware-related and you have a known clean backup, restore the database from the backup.

4.To see if these 824 errors can be reproduced outside of regular SQL Server I/O requests, use the SQLIOSim utility. SQLIOSim is included with SQL Server 2008, so there is no need for a separate download on this or later versions.
Collaboration with your hardware vendor or device manufacturer is required to ensure:
The hardware devices and configuration meet SQL Server’s I/O requirements.
All devices in the I/O path’s device drivers and other supporting software components are updated.

 

Alternate Solutions

1. Restarting SQL Server Service(non production instances only)

• To Restart, Start or Stop the SQL Server instance by right click on sql server instance in SSMS or in SQL. You may need to open SSMS as administrator to start, stop the instance.

• Other ways for restarting SQL server Service

1. From SQL Configuration manager from Start menu
2. From Services in Windows server
3. From Cmd using net start and net stop

2.Checking SQL Performance metrics like CPU, Memory

Check SQL Server CPU, Memory usage, longest running queries, deadlocks etc.. using activity monitor or sp_who2.

To view Activity Monitor in SQL Server 2005 and in SQL Server 2008, a user must have VIEW SERVER STATE permission.

2 Different Ways to Open up Activity Monitor in SQL Server 2008 are mentioned below:

Open up Activity Monitor Using Object Explorer

In Object Explorer, right click the SQL Server 2008 Instance and click on Activity Monitor.

Also can be opened from SQL Server 2008 Management Studio’s toolbar, by clicking Activity Monitor

It shows the graphical display of Processor Time (%), Number of Waiting Tasks, Database I/O (MB/Sec) and the Number of Batch Requests/second.

For information on SQL Server Activity monitor go to https://sqlserver-dba.co.uk/sql-server-administration-basics/activity-monitor

Or using SQL Query analyzer window to run sp_who2 command which is less resource intensive and gives same information as activity monitor.

2.Checking Windows Performance metrics like CPU, Memory, Disk Space etc.

1. Open task manager to check CPU, Memory usage etc.
2. Open file explorer to check Disk space on each drive.

 

SQL Server Error Code and solution summary

SQL Server Error: 824
Severity: 24
Event Logged or not: Yes
Description:
SQL Server detected a logical consistency-based I/O error: %ls. It occurred during a %S_MSG of page %S_PGID in database ID %d at offset %#016I64x in file ‘%ls’. 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.

Solution in short
Step 1. Examine the suspect page table in msdb thoroughly to ensure that no other pages are experiencing the same error.
Step 2: Using the built-in DBCC CHECKDB command, check the SQL server database’s consistency.
Step 3: If the PAGE VERIFY CHECKSUM database is not enabled, enable it as soon as possible.
Step 4: Run the SQLIOSim tool to check the status of the SQL 824 error.
Step 5: Determine whether the error was caused by the operating system, drivers, or storage device. Check the Windows event logs.

Before attempting to resolve the error, it is recommended that you create a backup of the SQL server database. To create backups, follow these steps:
All SQL services should be shut down.
Now move the corrupted database to a new location on the drive and save it.
Restart SQL server, and then create a new SQL database with the same name.
Separate the new database from the original database and run the commands/query listed below.

1. EXEC sp_resetstatus ‘db_name’ ;
2. ALTER DATABASE db_name SET EMERGENCY
3. DBCC CHECKDB(‘db_name‘)
4. ALTER DATABASE db_name SET SINGLE_USER Mode
5. With ROLLBACK IMMEDIATE DBCC CHECKDB (‘db_name’ , REPAIR_ALLOW_ DATA_LOSS)
6. ALTER DATABASE ‘db_name’ SET MULTI_USER