Monday, 5 August 2013

How to Eliminate Consistency Error of the SQL Server 2012 Database?

1 comment
SQL Server 2012 is integrated with advanced features that ensure high security for SQL databases. In spite of advanced security features, SQL Server 2012 databases or .MDF files may generate corruption issues due to multiple reasons as mentioned below:
  • Corrupt hard disk
  • Slow running of MS SQL Server 2012
  • Forced shutdown of MS Windows due to power fluctuation
  • Abnormal growth of the SQL 2012 database
  • Migration of SQL 2012 database into another SQL Server version
  • Virus and spyware intrusion into a computer
  • Defects in other computer hardware components

Database inconsistency is the most common issue in the SQL Server 2012 or any other SQL Server version. In database inconsistency state, both transactional log and primary database of MS SQL Server shows different data. Consistency issues on MS SQL Server are generated mainly due to non-availability of disk space and crash of the SQL Server. Database inconsistency may appear in some or all pages of the SQL database file. You shall see following messages when SQL Server finds inconsistency or page mismatch in the SQL database:
  • Error "0xe0008443 - One or more SQL Database consistency checks have failed
  • Replica is inconsistent
  • Data row does not have a matching index row in the index
  • SQL Server detected a logical consistency-based I/O error
Consistency errors in the SQL database are reported either during backup generation or consistency check process. 

Resolution:
Microsoft SQL Server 2012 is equipped with set of DBCC scripts (T-SQL) that can detect and fix SQL database consistency issues. DBCC scripts can be accessed in the SQL Server Management Studio or Studio Express. You can use following DBCC scripts or commands of MS SQL Server 2012 to fix SQL database consistency errors:

  • DBCC CHECKALLOC
  • DBCC CHECKDB
  • DBCC CHECKCATALOG
  • DBCC CHECKTABLE
  • DBCC CHECKFILEGROUP
DBCC scripts help SQL administrator in performing a comprehensive consistency check of the SQL database in disk allocation structures, catalogue, index pages and tables. Suppose, you want to recover database table named XYZ production, then use following T-SQL script:
  • DBCC CHECKTABLE (XYZ production, REPAIR REBUILD)
This script will rebuild missing or corrupt indexes of the XYZ Production table. It will restore all missing rows of non-clustered indexes in XYZ Production table. If you want to recover XYZ Production table then use REPAIR_ALLOW_DATA_LOSS in the T-SQL script as shown below:
  • DBCC CHECKTABLE (XYZ Production, REPAIR_ALLOW_DATA_LOSS)
However, certain amount of data in the XYZ Production may be lost while using REPAIR_ALLOW_DATA_LOSS Database recovery with DBCC scripts is time consuming. If consistency issue in a SQL 2012 database or database group is generated due to hardware malfunctioning, it is better to restore database file using either SQL backup Exec or third party software.


Kernel for SQL database recovery is an extraordinary tool to recover corrupt SQL databases. It swiftly recover inaccessible SQL MDF databases either from the local disk or Live Server. This MDF file recovery software restore every items from a corrupt SQL database that includes indexes, primary keys, tables, triggers, stored procedures, foreign keys and views. MDF file recovery tool successfully restore large SQL Server databases. It smoothly fix consistency errors of the SQL database. MDF file recovery software is compatible with all SQL Server versions including SQL Server 2012. See more at http://www.softwaredatarecovery.net/sqlrecovery.html

1 comment:

  1. It very good recovery tool to recover corrupt SQL databases. It swiftly recover inaccessible SQL MDF databases either from the local disk or Live Server. Keep it up for more related post.

    ReplyDelete