SQL Server Errors and Problems

When using Microsoft SQL Server to attach or access a corrupt MDF database file, you may encounter a variety of error messages that can be confusing. Below, we will list all errors, sorted by frequency. For each error, we will outline its symptoms, explain the exact cause, and provide sample files along with the files fixed by DataNumen SQL Recovery. This will help you better understand these errors. Note ‘xxx.MDF’ will represent the name of your corrupt SQL Server MDF database file.

Based on SQL Server or CHECKDB error messages, there are three kinds of errors:

1. 1. Allocation errors: We know the data in MDF & NDF files are allocated as pages. And there are some special pages that are used for allocation management, as follows:

If any of the above allocation pages have errors, or the data managed by these allocation pages are inconsistent with the allocation information, then SQL Server or CHECKDB will report allocation errors.

• Consistency errors: For pages that are used to store data, including the data pages and index pages, if SQL Server or CHECKDB find any inconsistency between the page contents and the checksum, then they will report consistency errors.
• All other errors: There may be other errors not fall into the above two categories.

• xxxx.mdf is not a primary database file. (Microsoft SQL Server, Error: 5171)
• The header for file ‘xxxx.mdf’ is not a valid database file header. The FILE SIZE property is incorrect.（Microsoft SQL Server, Error:5172）
• SQL Server detected a logical consistency-based I/O error: incorrect checksum
• SQL Server detected a logical consistency-based I/O error: torn page
• You delete some records or some tables in database by mistake.

SQL Server has a built-in tool called DBCC, which has CHECKDB and CHECKTABLE options that can help to repair a corrupt MDF database. However, for severe damaged MDB database files, DBCC CHECKDB and CHECKTABLE will also fail.

Consistency errors reported by CHECKDB:

• SQL Server detected a logical consistency-based I/O error: incorrect checksum
• Row in sys.xxx does not have a matching row in sys.xxx.
• Table error: Object ID ##, index ID ## will be rebuilt.
• This system table index cannot be recreated.
• Object ID ##, index ID ##, partition ID ##, alloc unit ID ## (type Unknown), page ID (##:560) contains an incorrect page ID in its page header.

Allocation errors reported by CHECKDB:

• The Index Allocation Map (IAM) Page is Pointed to by the Next Pointer of IAM Page
• Extent (##:##) in database ID ## is marked allocated in the GAM, but no SGAM or IAM has allocated it.

All other errors reported by CHECKDB:

• Failed:(-#######) Executing the query “DBCC CHECKDB(xxxx) WITH NO_INFOMSGS” failed with the following error: “xxxx”.
• I/O error (bad page ID) detected during read at offset 0x###### in file ‘xxxx.mdf’.
• System Could Not Activate Enough Of The Database To Rebuild The Log
• Data Loss When Repairing With CHECKDB
• File appears to have been truncated by the operating system.
• During redoing of a logged operation in database ‘xxxx’, an error occurred at log record ID.