Ошибка согласования sql

DBCC CHECKDB (ABCD) WITH NO_INFOMSGS, ALL_ERRORMSGS

Executing above command highlights the problem in detail, and SQL database may display the I/O  error message as follows:

Msg 8906, Level 16, State 1, Line 1

Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:1846) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).

Method 1 – Check hardware and application connectivity 

Database inconsistency error can be resolved by establishing proper connectivity between the database and SQL application. 

Method 2 — Restore from SQL backup

The most feasible solution is to use backup for SQL database restoration. Before restoring from backup, check that: 

Note: Page-level restoration is valid when you are dealing with large database and corruption is in only in one-page of SQL database.

Step 1: Use the following command for restoring SQL database from full backup

Backup the transaction log
BACKUP LOG PageLevelRestores TO
DISK = ‘g:PageLevelRestores_LOG1.bak’
WITH INIT
GO

Step 2: Perform the restoration change to reflect the changes online.

Backup the tail of the log…
BACKUP LOG PageLevelRestores TO
DISK = ‘g:PageLevelRestores_LOG_TAIL.bak’
WITH INIT
GO

Note: There is no need to restore full SQL server database if corruption is confined to a single page. You can restore database from the available backup for that page which is corrupted. Performing the following commands will help in restoring backup for a single page:

Restore all available log backups in the correct order
RESTORE LOG PageLevelRestores FROM
DISK = ‘g:PageLevelRestores_LOG1.bak’
WITH NORECOVERY
GO

— Finally restore the tail log backup
RESTORE LOG PageLevelRestores FROM
DISK = ‘g:PageLevelRestores_LOG_TAIL.bak’
WITH NORECOVERY
GO

— Finally finish with the restore sequence
RESTORE DATABASE PageLevelRestores WITH RECOVERY
GO

Once the database backup has restored SQL database, run the query DBCC CHECKDB again to check that the select statement succeeds without SQL Database logical consistency-based I/O error. This command also checks that there is no data loss in this table. 

Limitations of SQL database backup:

1. It is not possible to restore from SQL database backup when the available backup is obsolete.
2. If Logical consistency based I/O error is spread across the SQL server database, then this method will not be valid.
3. For those cases where faulty page exists in a non-clustered index, the SQL database can be fixed by dropping and recreating the index of SQL database. 

Alternative Solution

If above methods won’t work then, give a try to a SQL Recovery Software. Stellar Repair for MS SQL repairs corrupt MDF and NDF files and restores all the database objects. It supports MS SQL 2019 and all the lower versions. 

Most Effective Tactics to Fix Database Consistency Error in SQL Server

This is the most commonly query faced by SQL users. Here we will going to discuss the solution on how to fix Database Consistency error. But first, let us discuss some keypoints this blog will cover.

• Possible reason behind Database Consistency error
• How to check Database consistency error in SQL Server
• How to Fix Consistency Error in SQL Server Database?

DBCC CHECKDB Consistency Error

CHECKDB found 0 allocation errors and 4 consistency errors in database 'TestDB’
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (TestDB).
DBCC execution completed. If DBCC printed error messages, contact your system administrator. 

• Check Windows System Event Log- It will let you know the issue in system level, driver or disk related error.
• Run CHKDSK command- It will check the integrity of the file system.
• Work with Hardware Vendor – It will ensure you whether the configuration of hardware devices or supporting software components are updated or not.
• Try SQLIOSim  — It is an independent tool which test the integrity check of I/O disk system.
• Check Access Violations and Assertion for any SQL error.
• Make sure that uour database is using PAGE_VERIFY CHECKSUM option. If checksum error is being reported then your disk subsystem is thoroughly checked.
• Check error logs if it indicates SQL msg 832. It indicates the page get damaged when it is in cache.
• Restore Database backup and transaction log backup.

How to Fix Consistency Error in SQL Server Database

When DBCC CHECKDB report consistency error, the best solution is to restore it from good backup. But what if you fail to restore from backup? Then DBCC CHECKDB provides you an option to fix consistency error.But, if you are facing issue in hardware or file system, make sure to correct them before restoring or running the repair command.

Run below T-SQL command to attempt repair:

ALTER DATABASE Db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DBCC CheckDB ('Db', REPAIR_ALLOW_DATA_LOSS)
GO 

ALTER DATABASE Db SET MULTI_USER

Important Note: The repair option is considered as the minimum level of repair to resolve errors from DBCC CHECKDB. It means, if you run repair command, that does not mean that you will fix all errors. Not all errors generated by DBCC CHECKDB require this level of repair to fix this issue. And not all repair error will result in data loss.

One option to know the repair level for each table is to run DBCC CHECKTABLE, this will show the minimum level of repair for a given table.

If the repair option does not work to repair SQL Database corruption, then the only option is to restore or repair SQL database from recent backup.

Quick Way to Fix Database Consistency Error in SQL Server

REPAIR_ALLOW_DATA_LOSS is the minimum level of repair option. It doesn’t guarantee fixing SQL errors. For that case, you can switch to the third party utility ie SQL Database Repair Tool to recover SQL Database & make the database in the consistent state. You just need to perform few steps:

Download SQL Database Recovery Purchase SQL Recovery

Conclusion

The blog covers on how to fix consistency errors in SQL Server Database error. You will also get to know how you can you find out the cause of Database consistency Error in SQL Server. You can fix it manually by using commands or can fix by an automated approach, both described in the above blog.

SQL Server provides its users with built-in methods or solutions when they encounter different type of errors. These errors create problems to the users as the database goes inaccessible. For such conditions, the user goes for DBCC CHECKDB help to check the logical integrity of the database. But sometimes, while running the command, an error may appear like this;

2010-03-31 22:07:06.34 spid53      DBCC CHECKDB (mydb) executed by MYDOMAIN\theuser found 15 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.  Internal database snapshot has split point LSN = 00000026:0000089d:0001 and first LSN = 00000026:0000089c:0001.  This is an informational message only. No user action is required.

This is a general type of error log. While executing DBCC CHECKDB, different type of data consistency errors may be encountered. This error log shows the number of consistency errors. Internal database snapshot is for online DBCC CHECKDB as it is used to present a consistent data to check. These errors indicate corruption in SQL server database and makes data inaccessible to the user. Hence, indicates a dire need to resolve these consistency errors to get back access to the SQL Server data.

Common reasons for data consistency in SQL server database

DBCC CHECKDB, a set of commands checks the integrity of the database using logical commands. Due to any error or disturbance, when this operation fails, you get a consistency error log. In database consistency, MS SQL server primary database and transactional log shows different data. Some of the reasons for this consistency may include

• Less or no disk space
• Crash of SQL Server (Bugs inside database code)
• Hardware failure
• Power outages
• Upgrading SQL version
• Network issues
• File system corruption
• Corrupted pages in memory
• Issue with SQL Server Engine

One or more reasons might be responsible for the error. The error can be prevented by not overlooking the reasons for it. Being careful about the different causes that might affect your database is helpful any day. Once the reason is specified or known, you can move for the solution. Even if the reason is not completely identified, you may try to resolve data consistency errors by yourself. The problem always holds a solution for it.

Manual tricks to solve data consistency errors in SQL Server database

To solve the issue, you must primarily check for any fresh backup. If the valid, full and updated backup is available, you can restore the data. But such conditions are rare when a user is looking for some solution online. What to do when you do not have any backup? The solution is definitely there. The help is provided by Microsoft like DBCC CHKDB and DBCCDBREPAIR which are the database console commands. You can run these commands on SQL Server Management Studio to repair corrupt SQL Server database.

Using SQL Server Management Studio application

To start with you need to have SQL Server Management Studio application. This is a tool provided by Microsoft to manage and configure all components in Microsoft SQL Server. It comes with SQL Server itself. Now to resolve the SQL Server database corruption, follow the steps under.

Step1 Open the Microsoft SQL Server Management Studio application on your system.

Step2 On a window opened, click New Query icon.

A new query page will get opened.

Step3 Next, on the new query page, write the following SQL Scripts

EXEC sp_resetstatus [YourDatabase];
ALTER DATABASE [YourDatabase] SET EMERGENCY
DBCC checkdb ([YourDatabase])
ALTER DATABASE [YourDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC DATABASE ([YourDatabase], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [YourDatabase] SET MULTI_USER

Note: No change for the above query is required except for replacing ‘Your Database’ with the original name of your database file.

Step4 Click on the Execute tab. This is the final step.

You can try checking your database after the execution of this tool. The database may be accessible now. One more method to repair corrupt database is discussed here.

Using DBCC scripts

You can use DBCC scripts in SQL Server Management Studios that helps in detecting and fixing SQL database consistency errors. It helps SQL administrator to have a consistency check of SQL database in index pages, table and disk allocation structures. An example is shown below –

If you need to recover database table named ABC production, use these T-SQL script:

DBCC CHECKTABLE (XYZ production, REPAIR REBUILD)

It will rebuild missing or corrupt indexes of ABC Production table. It also restores all missing rows of non-clustered indexes in XYZ Production table.

Checking Windows System Event Log

Users can perform a check over the Windows System Event Log and if there Error Event logs available, then problem can be I/O related which can be easily resolved by performing the provided resolution for the particular error log. Other options would be running a check over system drivers, system level or driver issues. Moreover, confirming the I/O requirements for the installed SQL Server is also recommended.

Perform DBCC CHECKDB with Minimum Repair

As mentioned in the consistency error message while running the DBCC CHECKDB command for the SQL Database, users can try to run the minimum repair option to resolve all errors from running this command.

“CHECKDB found 0 allocation errors and 15 consistency errors in database ‘mydb’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (mydb)”

In basic language, it means that users can try to find out the minimum repair level to fix the error which he can achieve by trying to run DBCC CHECKTABLE command for each SQL table with error.

Check Hardware Requirements

Users can take help from the hardware/device manufacturer to ensure that the installed hardware conforms with the I/O requirements of the SQL Server as well as other device drivers and installed software are updated to the latest version.

Check File System Integrity with CHKDSK Command

Using CHKDSK command for checking the file system integrity would be a good trial. This command is effective in listing and correcting the errors available on the disks.

chkdsk [drive**:] [/p**] [/r]

These manual tricks are helpful in some cases, for minimum level of corruption but not always beneficial and trustworthy. Backup option is useful in some cases but for corrupt database elements cases, it does not work actually. By the time you decide to backup your data, corruption related to database elements has already crashed your backup as well.

When the degree of corruption is of high level, no manual trick works efficiently. While using repair option using scripts, there is always a chance of data loss. The method used is also very complex and time consuming. All these drawbacks of manual tricks call for an alternative solution.

Using a professional solution to resolve SQL Server database consistency error

Manual methods are certainly aid for the user in his bad time. The user can try it if unknown to large corruption. Sometimes, manual methods do work if the corruption is within control or small. With the learning of how manual methods work and response, users are encouraged to find a more reliable and secure alternative. The search is over with a reliable and efficient third-party tool. It is a highly innovative tool that recovers data from corrupt SQL server database within minutes. The tool is bind to provide high accuracy with recovery of file objects like tables, views, triggers, programmability, default, etc. The interface of the tool is quite user-friendly. Even a novice user is able to handle it easily. No technical expertise is required for the tool. SQL Recovery tool is the best solution for accurate and secure recovery of data from corrupt SQL Server database.

Download Now

Conclusion

Along with the discussion of data consistency errors and its causes, the user has come across the manual solution approaches, i.e. using DBCC CHECKDB repair command, restore from update and valid backup, checking file system integrity with CHKDSK command, running DBCC scripts, etc. It holds both benefits and drawbacks. However, a third-party tool is a savior in the worse conditions of database corruption.

Summary:-The blog highlights the Database consistency error in SQL server along with various reasons behind it and the possible manual and automated resolutions. This error is usually reported by making the DBCC CHECKDB command. So, let’s have a look at this particular error. You can either use the SQL Database Recovery tool or use some manual techniques to fix this error.

For the Database administrators, protection and recovery of the database is the primary task. Generally, the SQL Database works smoothly but at some point of time, it encounters a few errors and thus becomes inaccessible. When users try to execute a DBCC CHECKDB command in the database to check the integrity of the disk. After this, a database consistency error will be displayed in the SQL server.

So, not wasting time anymore, let’s dig out the various reasons that lead to the occurrence of database consistency error in SQL server.

Reasons behind the Database Consistency Error

The DBCC CHECKDB command in the SQL server checks the logical and physical consistency of the database that includes the index relationships, tables, rows, pages etc. If any of these consistency checks fail, an error will be displayed as the command is executed.

There are many reasons that make the occurrence of database consistency error as reported DBCC CHECKDB command.

• Issue in SQL Server Engine
• Corrupt pages in memory
• Hardware system issues
• Corruption in file system
• Drive related issues

So, let’s move to the solutions to troubleshoot the SQL Server database consistency error after knowing the reasons.

Methods to Fix Database Consistency Errors in SQL Database

After execution of DBCC CHECKDB command, if there is a failure in the database, a consistency error occurs. This error needs to be fixed in order to ensure smooth functioning of SQL Server. The best solution to repair the database consistency error is to restore the backup data. However, if the users are not able to restore the backup data, CHECKDB can be used to repair the errors. A user must correct the file system or hardware first as it is the main cause of corruption.

The manual solutions to resolve the SQL Server database consistency error has been explained below.

Check the Windows System Event Log

There are some I/O issues due to Windows System Event Log error as a result of which the above-mentioned error occurs. Therefore, a user should run a Windows system event log check for all kind of error. On the basis of inaccessibility of data, there are different event IDs and different solutions for each of them.

Perform the File System Integrity Check

File system integration is another reason behind the SQL database inconsistency. So, it is recommended to perform a system check using the command chkdsk. Using this command, you can get a complete report of the disk status.

Confirm the I/O Requirements of the SQL Server

There are some counters related to I/O available in the SQL Database. All of the counters are under some physical and logical disk. These counters are contained in the objects and can handle the logical operations of the hard disk on the system.

Check SQLIOSim Tool

It is another application that can be the reason behind the database consistency error. This tool is used for testing the I/O integrity of the disk system and is independent of the SQL server engine. It can be downloaded simply from the web.

Use the Option of Minimum Repair

When a user runs the DBCC CHECKDB command, the minimum repair option is run using an approximation that is important to fix the errors.

The following message will be displayed:

repair_allow_data_loss is the minimum level of repair for the errors as found by DBCC CHECKDB (AdventureWorks).

It is the minimum repair level to troubleshoot the database consistency errors in SQL server reported by DBCC CHECKDB. But, it is not necessary that every single file will be fixed by this repair option. It just displays those errors which have not been reported by DBCC CHECKDB option.

Alternate Solution to Fix Database Consistency Errors

Sometimes, there is a situation, when all the above methodologies don’t work. So, it is recommended to use a professional tool i.e. SysInfoTools SQL Database Recovery for an efficient and reliable approach. This software is very effective to repair the MDF files in the SQL Database. It restores all the database objects without altering any of the data and information. Solution to Restore Table From SQL Backup. 

Bottom Line

In the above technical script, we have explained the causes of the occurrence of database consistency error in SQL server. Also, possible manual, as well as automated solutions have been discussed to resolve the error as reported by the DBCC CHECKDB command or if the DBCC CHECKDB fails. Hope the article helps.