Blogs Home

Microsoft SQL Server is a reliable and advanced database management system that provides you various database management and SQL database recovery algorithms. DBCC CHECKDB is one of the most advanced SQL Server commands that helps you repair corrupt database. However under some situations, this command fails with an error and cannot repair damaged database. This behavior leads to critical data loss and requires SQL Server recovery to be fixed.

DBCC CHECKDB command may not recover SQL Server database due to various reasons, such as metadata structure corruption, system table damage, and faulty database index. For instance, consider the following error message that occurs when you try to perform MS SQL recovery using DBCC CHECKDB command-

"Database MyDB: Check Data and Index Linkage...

Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8921: [Microsoft][ODBC SQL Server Driver][SQL Server]CHECKTABLE terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is corrupt. Check previous errors...

An IAM (Index Allocation Map) is a page in the database that stores one bit per extent in all the GAM (Global Index Map). A set bit is used to point out that the extent is allocated to that particular index. At times, this convention is not followed as two IAM pages refer to the same GAM interval. Lot of times, this problem is caused due to database corruption, which in turn, can occur because of virus infections, database header corruption, abrupt or unplanned system shutdown, etc. There are several methods that can be used to achieve MDF file recovery. However, if you fail to do so then you should use a third-party SQL recovery software to do the needful.

Let us assume a situation in which you are getting the following error message while working on the MS SQL Server 2000 database:

“Table error: Multiple IAM pages for object ID O_ID, index ID I_ID contain allocations for the same interval. IAM pages P_ID1 and P_ID2.”...

MS SQL Server database is build up of various metadata structure components like, pages, extent, etc. Pages are the smallest unit of data storage that store actual data. A combination of 8 adjacent pages makes an Extent. Corruption or damage in metadata structure primarily results in inaccessibility of records stored in your database. In most of the database corruption situation, you encounter an error message stating the cause for the error. For successful access of records stored in your database and overcoming hazards created by error message, an easy way is to restore the database records from an updated backup. But if in case, your backup file has been corrupted, damaged, or unreadable, then you will need to use advanced repair software that can comprehensively repair MDF files.

Consider a practical case, where you encounter the below error message when you attempt to access the records stored in your MS SQL Server database:

"Table error: Extent P_ID object ID O_ID, index ID I_ID is beyond the range of this database."...

Microsoft usually recommends you to use a SAN (Storage Area Network) or locally attached hard drives to store your MS SQL Server database (MDF or Master Database File) because it optimizes the reliability and performance of Microsoft SQL Server. Use of the network database files (the files stored on NAS [Network Attached Storage] or networked server) isn't enabled for MS SQL Server. However, this configuration may damage your SQL Server database in some cases of system failure. In such situations, you must have an absolute backup of your significant database to extract inaccessible data from it. In the other case, you need to go for master database file recovery solutions to get your valuable data back.

Although, network file support is not enabled by default in Microsoft SQL Server, you can manually configure MS SQL Server to store all your database files on NAS storage server or networked server. The servers, which you employ for this configuration, must fulfill MS SQL Server requirements for write-through guarantees and data write ordering...

The DBCC CHECKDB command in SQL Server databases is used to check and repair any inconsistency in the databases. There are many extensions for this command that you can apply in various kinds of situations. However, at times when you this command finds any error on the system tables then it ends abruptly. Such cases can arise due to corruption in the database tables. In such situations, you should employ certain in-built workarounds to get the database repaired. If you are not able to recover the database using these methods, then you should use a third-party SQL database recovery software to recover SQL database.

Consider a scenario wherein you encounter the following error message while working on an SQL Server 2008 database:

"System table pre-checks: Object ID O_ID. Page P_ID has unexpected page type PAGETYPE. Check statement terminated because of an irreparable error."

Cause:...

Like other databases and files, Microsoft SQL Server database may also come across various corruption issues. The corruption can take place due to various reasons, including the internal database or SQL Server application issues and system related problems. One major system related problem, which can cause MDF (Master Database File) corruption, is I/O logical check failure. In such situations, the database becomes totally inaccessible and you come across severe data loss situations. In order to get your mission critical data back, you need to repair and restore the corrupt database using SQL database recovery solutions.

The I/O logical check failure can be indicated by various error messages, which includes the following one:

“Error 823 I/O error <error> detected during <operation> at offset <offset> in file '<file>'”...

In Microsoft SQL Server, if any transaction violates the integrity constraints during execution, database inconsistency occurs. The transaction leaves your MS SQL Server database in illegal state and it gets aborted with an error. Database inconsistency leaves your MDF (Master Database File) in an unusable and inaccessible state, which can lead to serious data loss. In such cases, a valid and updated backup come for your rescue. You can easily restore all your valuable data from current backup and prevent needs of SQL recovery using third-party applications.

However, in case you are unable to restore data from the backup or backup is incomplete, you can check the database errors and fix them using inbuilt utility of Microsoft SQL Server, called CHECKDB. It offers you an option to repair the database errors. Database inconsistency may also occur due to system level errors, like file system and hardware. You are highly recommended to fix these errors before repairing the database, else you may face the same problem again after some time...

In SQL Server 2008, the Auditing feature has been introduced that allow you to track user and system activity for the enterprise customers. This feature provides the option to detect any unauthorized access attempts, if any, to the SQL Server database. Also, it facilitates a rich and powerful tracking capability to recognize any sensitive configuration changes to the database by the administrators. However, at times the auditing process can fail thereby making the database get corrupt. You should use appropriate corrective measures to rectify the problem. If you are not able to address the situation, then you should use a third-party SQL recovery tool software to recover SQL database.

Consider a scenario wherein you encounter the following error message while auditing a database table in SQL Server 2008.

“Table error: alloc unit ID A_ID, page P_ID. The test (TEST) failed. The values are VAL1 and VAL2.”

Cause:...

Are you confronting a 'Severity Level 16' error message while trying to repair your database using DBCC CHECKDB command? If yes, then you are among many database users who are encountering severe logical database corruption. Few factors responsible for high level MS SQL Server database corruption are improper server shutdown, virus infections, and application malfunction. Records saved in the database remain inaccessible in most situations of DBCC CHECKDB failure error message. To access the records and to surmount the error message, an easy alternative for the database user is to perform restoration from an updated backup. However, in case no database is available, you are left with only one option – repair the database. To systematically do so, you need to opt for a powerful, yet safe commercial MDF Database Repair utility.

To elaborate the above case, consider a practical scenario wherein your database table is severely corrupted and you use DBCC CHECKDB command to repair the table. However, while doing so, you encounter the below error message:...

Pages in metadata structure of MS SQL Server database are either linked in a B-tree or heap tree format. These pages contain the records of different database components. In such situations, these pages instead of pointing the required pages, start pointing to a wrong page. The database records become inaccessible in case the SQL database gets corrupted or damaged. To access the records saved in the database components, an easy way is to restore the database objects from an updated backup. But, if in case the backup is not available, or backup file is not created, then you will need to repair the SQL database to access all its records. To do so, you will need to use advanced third-party SQL Recovery utility.

To briefly explain the above database corruption scenario, below is the error message that pops up when a SQL database table gets corrupted:

“Table error: Cross object linkage: Parent page P_ID1, slot S_ID1 in object ID O_ID1, index ID I_ID1, and page P_ID2, slot S_ID2 in object ID O_ID2, index ID I_ID2, next refer to page P_ID3 but are not in the same object.”...

A table in MS SQL database can be corrupted due to various reasons. One of the most common being the corruption in metadata structure, caused due to damage of SQL Server damage. Once the table gets corrupted, it fails to display records stored in it. Furthermore, you also receive an "Table Error" message that does not allow you add any new records. An easy way to overcome such hazards is by restoring the table from an updated backup. But, with probabilities of backup file being corrupted, not updated, or inaccessible always exist. In such situations, the database user is left with no option, but to repair the corrupted table. To effectively do so, the user need to search for an advanced SQL Recovery application.

To illustrate the above case, below is a Table error message that appears after your SQL database table gets corrupted:

"Table error: Object ID O_ID, index ID I_ID, page P_ID. Test (TEST) failed. Slot S_ID, offset 0xADDRESS is invalid."...

Corruption of MS SQL database can result into damage of metadata structure entries, which can further result into inaccessibility of records saved in the SQL database components. In most cases of metadata structure damage, you receive an error message that specifies the exact metadata entry, which is corrupt. To overcome the error message and to access the database components records, an ideal way is to restore it from an updated backup. However, there are thousand of situations, where either the backup gets corrupted or the database administrator forgets to create a backup. In such cases, the only way to access records of a corrupted SQL database component is by repairing it with a powerful third-party MS Recover SQL application.

Consider a practical case, where you receive the below error message while trying to access a SQL database table:

“Table error: Object ID O_ID, index ID I_ID B-tree level mismatch, page P_ID1. Level LEVEL1 does not match level LEVEL2 from parent P_ID2.”...

A PFS (Page Free Space) page tracks the allocation status for every page and the amount of free space available on the page. The database engine uses the information stored in the PFS pages to allocate new pages. The page stores the information in the form of bytes, including a byte that indicates that the page is a mixed page. An IAM (Index Allocation Map) page, on the other hand is an array that stores all the mixed pages assigned to a particular index. The data stored in the database components becomes inaccessible if the page that is assigned a 'mixed page' byte in PFS page, not the same page does appear in the IAM page's array. This primarily occurs due to SQL database corruption. In such cases, the database becomes unmountable, further resulting into inaccessibility of its records. For complete accessibility of records in such situations, you will need to restore the data from an updated backup. But, if backup is not available, then you will need to use advanced MS SQL Recovery application...

The logical and physical integrity of all SQL Server database tables is performed by DBCC CHECKDB command. The command performs and divides its operations in three different phases. The first phase checks the consistency of the allocation structures in disk space, the second phase checks the integrity of all pages and their structures, and the last phase checks the catalog consistency. If critical errors are found in any of the phases, the DBCC CHECKDB command terminates immediately. This happens when the SQL Server database is badly corrupted, and is beyond the repairing capabilities of DBCC CHECKDB command. In such cases, if you need to access the database records, then you will need to restore the database records from a valid backup. But, if no backup is available or backup falls short to restore the required amount of data, then you will need to repair the database by using advanced MS SQL Server Recovery application...

Corruption in MS SQL database is categorized into two forms – Logical and Physical. While the physical database corruption scenarios include damage of system components, the logical corruption situations only relate to software issues. The outcome for database corruption caused due to either of the two reasons in most cases is, unmountability of the database. An unmountable database does not allow you to access the records stored in the database. In such cases, an up-to-date backup (usually maintained by all the database users) allows you to restore the data and continue with your work. However, there have been thousands of case studies where either the user forgets to create an adequate backup or backup file itself gets corrupt. For such situations, the user needs to search for an effective MS Recover SQL application (for logical corruption) and database repair services (for physical crash scenarios).

Some of the most common logical corruption scenarios after which the database becomes unmountable are mentioned below:...

An MS SQL Server database file is divided into various PFS (Page Free Space) intervals, approximately 64 MB in size. The byte-map of the PFS page consists of at least one byte per page for every page that is included in the PFS interval. The information stored in the PFS interval is very critical because corruption of it can result into database error, which stops you from accessing the records stored in the database. In such situations, the database corruption can be surmounted by restoring the data from an updated backup. However, the possibilities of database users forgetting to create an adequate backup, or the location where the backup is saved always exits. In such cases, the database user needs to opt for a powerful third-party SQL Server Recovery utility that can easily repair the database.

Picture a scenario, where you receive the underneath error message with your MS SQL Server database:

"Incorrect PFS free space information for page P_ID, object ID O_ID, index ID I_ID, in database ID DB_ID. Expected value PFS_VAL1, actual value PFS_VAL2."...

PFS (Page Free Space) pages in SQL Server database not only maintains free spaces, but a lot more than that. Every SQL database file is divided into various PFS intervals, each containing 8088 pages. A PFS page contains a byte-map, which stores one byte for every page included in the PFS interval. Corruption in PFS page results into damage in the stored bytes, further resulting in making your database unmountable. The database records saved in the database become inaccessible after it becomes unmountable. For easy accessibility of records and to save your valuable time, you will need to restore the data from a valid backup. However, in case of backup unavailability or corruption, you will need to use a third-party MS SQL Database Recovery tool, which provides systematic repair.

As a practical example, you receive the below error message while mounting your SQL database:

"Database error: Page P_ID1 is marked with the wrong type in PFS page P_ID2. PFS status 0xVAL1 expected 0xVAL2."...

Database corruption after unexpected system shutdown is a common scenario happened to almost all of us. The database becomes unmountable, further resulting in inaccessibility of records, after being corrupted. In most of these situations, you receive an error message that describes the exact cause of corruption, and sometimes its resolution too. However, if the resolution provided in the error message works, then there is no need to worry. But if in case, the resolution fails, then the best way to overcome database corruption is by restoring the data from an updated backup. Unavailability of a valid backup leaves you with no option, but to use advanced third-party SQL Database Recovery application to repair the database.

Consider a practical scenario, where your database unexpectedly stops and restarts again. On the restart, the database does not mount, and you view the below error messages in the error log file:...

Restoration of data from an updated database backup allows you to overcome almost all SQL Server database corruption scenarios. It is therefore, always recommended to maintain a systematic database backup on some other storage media as well. While restoration of data is an easy process, it might result into an error message, which prevents you from restoration of the database. In such situations, an easy way is to restore the data from any other updated database backup. However if in case, no other backup is available or backup falls short to restore the data, then you will need to opt for advanced third-party SQL Database Recovery application.

Consider a practical scenario, where you encounter the underneath error message while restoration of data:

"Msg 8967, Level 16, State 216, Server <server name>, Line 2 An internal error occurred in DBCC which prevented further processing. Please contact Customer Support. DBCC results for '<database name>'. Msg 8921, Level 16, State 1, Server <server name>, Line 1 Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors."...