SQL Server Database Corruption Detection

Introduction

Database corruptions do not occur normally, but when they occur, it is always a very troublesome and tense event for DBAs. Many times corruption is caused by hardware or subsystem problems. We, as DBAs, do not often have much to do in avoiding corruption, but we may try to detect them as early as possible. The earlier we discover it, the more options we have to fix the issues.

We are not going to talk about how to fix corruption. Instead, we will share various things DBAs can do to detect database corruption. We will discuss four topics as below:

1. Recovery - Page Verify setting

2. Database Backup with checksum

3. DBCC checkdb

4. Database Alert

Recovery — Page Verify Option

The Page Verify option is set on each database. It may be set from SSMS or by consuming a T-SQL script. This option expresses the database of how the page health is verified when a page is read or written.

To set this from SSMS, right-click on a database, and select properties. The Database Properties window opens. At the Options page, below the Recovery section, that is the Page Verify option. It is highlighted in the image below. Click the down arrow button on the far right to fix this.

To change the option by TSQL, using this code:

ALTER DATABASE <database name>

SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;

There are three choices you can set for these options

1. NONE does not page verification
2. TORN_PAGE_DETECTION is an ancient option, and not the best option if you are on SQL Server 2005 or above
3. CHECKSUM is the best one to choose from

If the database is formed in a newer version of SQL Server, by default CHECKSUM is set. If the database was upgraded/migrated from the ancient version, be sure to physically make a change this option to CHECKSUM. While it does not change all to checksum right way, the database will step by step change page verification to CHECKSUM when read/write occurs.

If a corrupted page is retrieved, SQL Server will show an error (823, 824, or 825). Be aware, if corrupted pages are not retrieved, no error will have occurred.

Backup With Checksum

Always run the backup database command with the checksum choice. As defined in Books Online: "Specifies that the backup operation verifies each page for checksum and torn page, if enabled and existing, and generate a checksum for the entire backup. Its approach that SQL Server is analyzing page in the course of the backup manner"

If corruption is detected, the backup will decline. If the backup job is set to send a notification when it fails, you are intimated by email.

As an example, your backup command should look alike to this:

xxxxxxxxxx

BACKUP DATABASE AdventureWorks

TO DISK='X:\SQLServerBackups\AdventureWorks.bak'

WITH CHECKSUM;

We would like to establish the backup job always forward a notification when the job completes, no matter if it declines or achieves. In this way, I am assured the job did run and is not for some causes.

DBCC CHECKDB

This is a detailed way of checking the whole database. DBCC CHECKDB covers it all. This database analysis takes a little bit of time to run, depending on the condition of your database size. Usually, it is arranged to run during a maintenance window. This should be run either daily or weekly, liable on what your system can spare for resource capacity. It is suggested that this be run weekly or more regularly. Whenever your database is not working appropriately, you need to follow the instructions below and find the SQL MDF file corruption.

Implement the following command to check the corrupt files:

xxxxxxxxxx

SELECT * From msdb.dbo.suspect pages

If you find no error in the database, no rows are shown in the result. It means that no corrupt entry was originated. However, this is not the meaning that there could be no broken data since the query only checks the entries that have been damaged in the past. Damages in a new entry are not analyzed. You can use the other command to check the bad pages.

DBCC CHECKDB is the commonly used command by database administrators to find the corrupt files and fix them. DBCC joins several other commands like DBCC CHECKCATALOG, DBCC CHECKALLOC, and DBCC CHECKTABLE. To check if SQL database is corrupted, You may run this as follows.:

xxxxxxxxxx

DBCC CHECKDB: Rename database_name with the name of your database.

DBCC CHECKDB(database_name)

This command forms the table, memory, quality, and consistency measures and puts the consequence in a list. When corruptions are detected, this job may do well with data in productivity or fail with an error message. We forward the job output to a text file and then let the job forward the text file to me via email. Again, the job is set to notify me no matter the job fails or succeeds.

Alerts

DBAs should be warned if any critical errors occur in a database. Glenn Berry has a good script to set these alerts up at this link: https://www.sqlskills.com/blogs/glenn/creating-sql-server-agent-alerts-for-critical-errors/

This check contains corruption errors as well, such as 823, 824, 825, etc.

Conclusion

Here, we discussed how to check if the SQL database is corrupted and what are the reasons behind SQL database corruption If corruptions occur, get notified ASAP. Make ensure you have good backups available all the time.