SQL database attach failed prompt 823 824 bug fix

SQL Server refines the error types of database corruption and introduces several typical errors here.

The error message is: "In file '%ls ', where the offset is% #016I64x, the operating system has returned error%ls to SQL Server during%s_msg. ”

"The operating systemreturned error%ls to SQL Server during a%s_msgat offset% #016I64x in file '%ls '."

For example:

MSG 823, level, State 3, line 1

The operating system returned error 5 (Access is denied.) to SQL Server during a write at offset 0x0000000000e000 in file ' F Ilepath\filename '.

A 823 error indicates that SQL Server encountered a failure in Windows read or write requests when it requested a page to read or write to the operating system. The error code returned by Windows and the corresponding text are inserted into the message. For a read operation, SQL Server has retried the read request 4 times before reporting a 823 823 error.

From the mechanism of the error, it can be seen that 823 824 errors occur when a page read and write requests are made, and there is no relationship between the read and write content. So the 823 error has nothing to do with SQL Server itself. This error is usually caused by physical file corruption, but it can also be caused by a device driver. If there is a 823 error on a data file, either the hardware is out of place or the data file has been badly damaged. This error basically means that the valid data in the data page has been lost, the general dbcccheckdb is difficult to repair.

The error message is: "SQL Server detected a consistency-based logical I/O error%ls. The error occurred during%s_msg execution of page%s_pgid in database ID%d in file '%ls ', where the offset is% #016I64x. ”

"SQL Server Detecteda logical consistency-based I/O error:%ls. It occurred during a%s_msg of page%s_pgidin database ID%d at offset% #016I64x in file '%ls '. "

For example:

SQL Server detected a logical consistency-based I/O error:tornpage (expected signature:0x0; actual signature:0x4e0372a8 ). It occurredduring A read of page (1:0) in database ID, at offset 0000000000000000 infile ' S:\Microsoft SQL server\mssql. 1\mssql\data\www71_global_data.mdf '.

This error indicates that Windows reported that the page was successfully read from disk, but that there was a logic error in the page detected by SQL Server. What are the "logic errors" that SQL Server detects? There are several common types of errors.

Checksum

SQL Server can write each page, according to the data in the page to calculate a checksum value, together stored in the page. The next time you read the page, based on the page data read, a new check value is calculated. If the data written and read are identical, then the two checksum values must be equal. If the two checksum values are not equal, it means that the data written by SQL Server last time is different from the one read, and there is a problem with the data being read. In this way, SQL Server can discover data page corruption.

Tornpage

Torn pages (torn page) protection is actually a method of detecting page corruption caused by a power failure. For example, an unexpected power failure could cause only a portion of a page to be written to the disk. When using torn page protection, a 2-bit signature is placed at the end of each 512B sector of the page (after the original 2 bits are copied to the page header). This signature alternates between binary numbers 01 and 10 each time a write is performed, so that it is always possible to determine whether only some sectors are written to disk. If the status of a bit is found to be incorrect later when the page is read, the page is not written correctly, so the problem page is detected, called the torn page. Torn page detection uses the least amount of resources relative to checksum, but its algorithm is too simple to detect any errors caused by a disk hardware failure.

The recent development of the SQL database physical file Repair tool can be effective for 823 824 error Fast correction, sql7.0 sql2000 if the Tornpage check page corruption will prompt 823, and SQL2005 and the above version whether tornpage or checks Um check will report 824 error once page corruption

Download Demo Video Https://pan.baidu.com/s/18IaYqOmiHJ4rEx7qnSNBOw

SQL database attach failed prompt 823 824 bug fix