Ready to Start Your Career?

How to Check for SQL Database Corruption?

robinshanab 's profile image

By: robinshanab

April 30, 2018

SQL Database is an integral part of the business. When a SQL database is deployed at a location, you may hear comments from end users such as “Database is running slow,” or “The database is not responsive” or “it hangs.” Though these are generic terms, the problem arises due to SQL database corruption. Massive databases are not easy to manage, and if there are unexpected issues like power failure, then the corruption in the database is apparent. Similarly, using SQL Server Compact Edition as a database for web server needs support for multiple users which requires data changes. Here’s how to check MS SQL Database corruption and find a relevant solution.

Note: CheckDB command lets you check SQL Database corruption in the SQL Database, but corruption in SQL server compact edition is verified with the help of Engine object.

Start Troubleshooting:

The first step to troubleshoot is to understand the nature and intensity of the issue. Interpret the problem and devise the Repair method.

The SQL server compact database files are bifurcated into 4 Logical KB units with each unit individually named as Pages. All pages are written to the database file. The SQL server compact performs a calculation and saves the checksums for that page. The Verify method of System.Data.SqlServerCe.SqlCeEngine class recalculates the value of checksums of every page available in the database file and if the values do not match its expected value, means the page is modified or corrupted.

To verify SQL Database corruption: Pinpoint the cause!

  1. Recalculate the checksums for each page in the database, and uses the following syntax:

                        HRESULT Verify(BSTR SourceConnection)

  1. Compare the new checksums to the expected values

          Following Parameters are available:

          Source Connection

          A string value is specifying a connection to the source database to be verified.

          Return Value

          The return type is HRESULT, and S_OK is returned on success.

Analyze the Results:

  • If the method returns ‘True," that means ‘No database file corruption.’
  • If the method returns ‘False’, it means ‘Database file is corrupt’, and the application requires the “Repair” method of the SqlCeEngine object
  1. Before starting with Repair method, try the VerifyEx option which is more thorough. The user can verify that each Index entry exists in the Table and each table entry exists in the index. Though this is a slower, VerifyEx is better than the Verify method.
  2. The Repair method scans the SQL server compact database and then calculates the page checksums.

Take Action:

Once, the Verify method of System.Data.SqlServerCe.SqlCeEngine class proves that the database file is corrupted, resolve the corruption error using the Repair method of the SqlCeEngine object.

Before opting for the Repair method, consider the following Parameters:

  1. SourceConnection: A string value which describes the connection to the corrupted source database
  2. DestinationConnection: Defines the string value which specifies the location of the newly created database
  3. RepairOption: Requires the type of database repair to perform.

          The RepairOption utilizes the following syntax:

          object.Repair(SourceConnection, DestinationConnection, RepairOption)

Following RepairOptions help in the recovery of SQL Server compact edition

  1. RecoverAllOrFail
    • The RecoverAllorFail value works best when there is ‘No Data Loss’ due to corruption in SQL database. In case, the RepairOption detects an error, while utilizing the syntax, then further recovery stops, and an error in the form of exception shows on the screen.
    • However, if there is no data loss, then the repair option of RecoverAllOrFail ensures protection against data loss as well as protection for the repaired database.
  1. RecoverAllPossibleRows
    • The RecoverAllPossibleRows method illustrates that the command will help database to read complete data including rows from the SQL database corrupt pages. Recovery is available for more data, but the biggest drawback of this method is that there is no surety if the recovered data is free from corruption.
  2.  DeleteCorruptedRows
    • The DeleteCorruptedRows method deletes all the corrupt pages during scanning, and this feature may create an alarming situation as the resulting data may incur significant data loss in case the corrupt database contains data rows or database schema. DeleteCorruptedRows method is a good option as it frees the database from any error.
  3. RecoverCorruptedRows
    • The RecoverCorruptedRows method tries to read data from the corrupted pages. As the syntax reads data from corrupted pages, the resultant scanned and repaired database consists of the larger database, but there are no fixed criteria if the database is free from logical corruption in SQL server compact edition.

An analysis of RepairOptions

The RepairOptions provide the following results:

  • RecoverAllorFail recovers all database but stops in the middle, in case an error is detected
  • RecoverAllPossibleRows recovers all data, but there is no surety if it is corruption-free
  • DeleteCorruptedRows deletes database in case corrupt database contains database schema
  • RecoverCorruptedRows does not have fixed criteria if the database is free from logical corruption

A thorough analysis of all the repair options indicates that none of the methods provides complete database recovery without loss.

Automated software as an Alternative

A practical solution is to use Stellar Phoenix SQL Database Repair software. Why choose Stellar software? The software is available with following features which makes it a better choice than the manual RepairOption methods:

  1. Repairs the corrupt SQL database including MDF and NDF files
  2. Recovers all components including Tables, Triggers, Indexes, Keys, Rules and Stored Procedures
  3. Recovers deleted records from SQL database
  4. Enables saving the repaired database in multiple formats
  5. Single software supports all MS SQL Server versions
  6. Repairs and recovers the database without applying Syntaxes
  7. Scans and Repairs database in few easy steps with the self-guiding user interface

Conclusion

Once the process of "check SQL database corruption" is complete, the SQL Administrator needs to decide the right means to repair the corrupt SQL database. However, an in-depth analysis of manual method versus automated method shows that the RepairOption manual method has some significant limitations. These limitations result in either incomplete SQL Database recovery or unresolved corruption issue. In both the cases, the Administrator and the User have to compromise. On the other hand, Stellar Phoenix SQL Database Repair software repairs the entire database and ensures its accessibility after complete recovery. The software also provides database logs which lets you identify the corruption along with its proof of repair.

Schedule Demo