How to Plan for Corruption Check for SQL Server Databases?

Database corruption is a thought that looms in every SQL DBA's mind. While events like corrupted data pages, queries failing with severity errors, etc., are evidence of database corruption, it can at times go unnoticed unless the database is down and won't restart. So, it's important to plan for SQL Server database corruption check.

Planning for Corruption Check for SQL Server Databases

Following are a few methods you can use when planning on running consistency checks on the SQL Server databases.

• Enable CHECKSUM for Database Pages
• Database Backup with Checksum
• Use TABLOCK Hint to Run DBCC CHECKDB without Error
• Run Script to Check SQL Databases for Fatal Corruption

Enable CHECKSUM for Database Pages

Setting the PAGE_VERIFY database option to CHECKSUM helps verify consistency of the database pages.

When CHECKSUM is enabled, and a database page is written to a disk, SQL Server database engine calculates the checksum for the whole page and stores the value in the page header.

When a page is read from the disk, the checksum is compared with the checksum value stored in the page header. The database page is considered corrupt if the checksum mismatch and SQL Server reports errors like 823, 824, or 825. 

Steps to enable VERIFY_PAGE database option in SQL Server Management Studio (SSMS) are as follows:

• From Object Explorer, right-click on a database, and click Properties.
• In the ‘Database Properties’ window, click Options under ‘Select a page’.
• Next, navigate to the ‘Recovery’ section, select CHECKSUM from the Page Verify drop-down list.

You can also change the PAGE_VERIFY db option to CHECKSUM by running the following TSQL code:

Run Backup Command with CHECKSUM

When performing a database backup process, run the backup command with the CHECKSUM option. A checksum is calculated over the entire backup.

You can easily detect if the backup file is modified or corrupted by running RESTORE or RESTORE VERIFY_ONLY. 

The backup command with CHECKSUM option is all follows:

Use TABLOCK Hint to Run DBCC CHECKDB without Error

While attempting to restore a SQL database 2008 from a backup, you may get errors causing the restore process to fail. Running the DBCC CHECKDB command on the database may further return the following error:

Msg 8967, Level 16, State 216, Server servername="", 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 servername="", 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.

If the CHECKDB command fails to run the necessary consistency checks on a database, such an issue occurs. The checks may fail due to a corrupted database snapshot or metadata inconsistencies.

You can fix the issue by executing the DBCC CHECKDB command with the TABLOCK hint – which helps the command run successfully without generating any error. See this Microsoft guide for detailed information about TABLOCK hints.

Run Script to Check SQL Databases for Fatal Corruption

Sometimes, severe corruption in a database may prevent DBCC from running. Or, if the DBCC CHECKDB command completes, it doesn't necessarily mean that the command completes successfully – if you check the end of the output you may find errors indicating that the CHECKDB command can’t continue. For instance, you may receive the below error.

Msg 7995, Level 16, State 1, Line 1
Database 'Database': consistency errors in system catalogs prevent further DBCC checkdb processing.

Paul Randal provides a good script to check for fatal corruption that prevents DBCC CHECKDB from running or executing successfully.

 If DBCC CHECKDB fails to get past fatal corruption in a SQL db, you won’t be able to run a repair operation. The only resort is to restore from a backup or try to extract as much data as possible from the database file.

What to do If SQL Server Finds Corruption?

If you encounter database corruption, before taking any action, read this blog ‘What to Do When DBCC CHECKDB Reports Corruption’ by Brent Ozar. In the blog, Brent discusses a checklist of the actions you must take when corruption strikes. These actions include disabling the backup-delete jobs, querying the 'msdb.dbo.suspect_pages' table, informing all the stakeholders involved about database corruption, etc.

Further, he talks about taking some proactive measures into considerations to prevent database corruption. Some of the measures are setting up email alerts to find I/O issues behind corruption, increasing the frequency of DBCC CHECKDB checks on databases, taking backups with CHECKSUM, understanding your RPO and RTO, and more.

Once you know things you need to do on encountering corruption in a SQL database, the NEXT step is to repair that database file or extract data from it.

How to Fix SQL Server Database Corruption?

Aside from having frequent and tested backups, which you can use to recover a recent copy of the data and rebuild the corrupt pages, well-prepared DBAs must also have the right tools to repair every bit of information without modifying the original data. Stellar Repair for MS SQL is one such tool you can rely on to fix your corrupted database file (MDF) and retrieve all the data.

The SQL repair tool uses a parallel processing technique to save multiple tables for faster database restore operation simultaneously.

Read this: How to Perform Faster Database Recovery using Parallel Processing?

Conclusion

Corruption can happen anytime, rendering the databases inaccessible and unavailable for use. But you should not wait for the SQL Server databases to turn corrupt. You can plan on running consistency checks on the databases using the methods discussed in this article, including enabling the VERIFY_PAGE option with CHECKSUM, running backup with CHECKSUM, and setting up alerts. If you encounter corruption in a SQL database, using Stellar Repair for MS SQL software can help you restore the database to its original state with all the data intact.

Was this article helpful?

YES0

NO0

About The Author

Charanjeet Kaur

Technical writer with over 7 years of experience