Disk I/O errors monitored by SQL Server, msdb. dbo. suspect_pages, and linux disk I/O

Disk I/O errors monitored by SQL Server, msdb. dbo. suspect_pages, and linux disk I/O

The suspect_pages table is located in the msdb database and is introduced in SQL Server 2005. Suspect_pages used to maintain information about suspicious pages

The Database Administrator is responsible for table management (mainly by deleting old rows ). The suspect_pages table has a size limit. If the table is full, no new errors are recorded. To prevent the table from being filled up, the database administrator or system administrator must manually clear the old entries in the table by deleting rows. Therefore, we recommend that you regularly delete or Archive rows whose event_type is restored, repaired, or with the old last_update value.

To monitor operations performed on the suspect_pages table, you can use the Database Suspect Data Page event class. Sometimes, rows are added to the suspect_pages table due to a temporary error. If you are adding many rows to the table, the I/O subsystem may be faulty. If you notice that the number of rows being added to the table suddenly increases, we recommend that you check whether the I/O subsystem is faulty.

The following table shows errors recorded in the event_type column of the suspect_pages table.

Error description	Event_typeValue
823 error caused by CRC error of the operating system, or 824 error other than checksum error or page tear (for example, page ID error)	1
Incorrect checksum	2
Incomplete page	3
Restored (the page is restored after it is marked as an error)	4
Repaired (DBCC fixed the page)	5
Released by DBCC	7

Temporary errors are also recorded in the suspect_pages table. The source of a temporary error includes an I/O error (for example, a cable disconnection) or a page that does not pass the duplicate checksum test at the moment.

How does the database engine update the suspect_pages table?

The database engine performs the following operations on the suspect_pages table:

If the table is not full, a 824 error occurs, and the table is updated to indicate that an error has occurred, and the error counter increases accordingly.
If an error still persists after the page is repaired, restored, or released, the number_of_errors count increases and the last_update column is updated.
After the listed pages are restored or repaired, the suspect_pages row is updated to indicate that this page has been repaired (event_type = 5) or restored (event_type = 4 ).

If you run the DBCC check, the check marks all non-error pages as fixed (event_type = 5) or released (event_type = 7 ).

Automatically update the suspect_pages table

When an attempt to read a page in the data file fails due to one of the following reasons, the database mirroring partner will update the suspect_pages table.

The 823 error caused by the CRC error of the operating system.

824 error (logical corruption like page tear ).

The following operations will automatically delete rows from the suspect_pages table.

ALTER DATABASE REMOVE FILE
DROP DATABASE
Dbcc checkdb REPAIR_ALLOW_DATA_LOSS updates the suspect_pages table to indicate the released or repaired pages.

The RESTORE can also update the list. Complete restoration, file restoration, or page restoration marks the page items as restored.

How can I save the created table to a disk in SQL Server?

Well, go to 'enterprise manager'-control root directory-> SQL server group-> open the corresponding database-> right-click the corresponding database and choose "all tasks"-> "Back up Database"-> 'normal', 'Back Up To 'Here to add, confirm the backup path and the backup file name ,. After this part is completed, click "OK'

Find the file generated by the backup under your backup path and save the file to the disk.

You can use this database to restore the file to SQL server2005 to obtain the corresponding data.

SQL2005 Database Error 824

Reference SQLServer help information:
This error indicates that Windows reports that the page has been successfully read from the disk, but SQL Server detects an error on the page. This error is similar to error 823, but is not detected in Windows. This usually indicates that there are problems in the I/O subsystem, such as disk drive failures, disk firmware problems, device drivers are incorrect, and so on.

Find hardware faults
Run hardware diagnostics to solve any problems. You can also check Microsoft Windows system and application logs and SQL Server error logs to check whether errors caused by hardware faults exist. Resolves all hardware-related issues contained in these logs. If persistent data corruption occurs, try using other hardware components. Check to ensure that the write cache on the disk controller is not enabled. If you suspect that the write cache is faulty, contact the hardware supplier. Finally, you may find that switching to a new hardware system is an excellent solution to the problem. This switch operation may include formatting the disk drive and reinstalling the operating system.

If the problem is not related to hardware and the known clean backup is available, restore the database from the backup.
Consider changing the database to use the PAGE_VERIFY CHECKSUM option.