Database corruptions can be a nightmare for DBAs. While preventing corruptions is not always possible, detecting them early can help minimize the impact and provide more options for fixing the problems. In this article, we will discuss several methods that DBAs can use to detect database corruptions.

1. Recovery – Page Verify Setting

The Page Verify option in SQL Server determines how the page health is verified when a page is read or written. It can be set for each database either through SQL Server Management Studio (SSMS) or using a T-SQL script. To set this option in SSMS, right-click on the database, choose properties, go to the Options page, and under the Recovery section, select the appropriate Page Verify option.

Alternatively, you can use the following T-SQL script to set the Page Verify option:

ALTER DATABASE <database name>
SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;

There are three options for the Page Verify setting:

• NONE: No page verification is performed.
• TORN_PAGE_DETECTION: An older option that is not recommended for SQL Server 2005 or above.
• CHECKSUM: The best option to choose. If the database is created in a newer version of SQL Server, CHECKSUM is set by default. If the database is upgraded/migrated from an older version, make sure to manually change this option to CHECKSUM.

When a corrupted page is accessed, SQL Server will raise an error (823, 824, or 825). However, if corrupted pages are not accessed, no error will be raised.

2. Backup with Checksum

Always run the backup database command with the checksum option. This option verifies each page for checksum and torn page during the backup process. If corruption is detected, the backup will fail. To enable this option, use the following syntax:

BACKUP DATABASE <database name>
TO DISK='X:\SQLServerBackups\<database name>.bak'
WITH CHECKSUM;

It is recommended to set the backup job to send a notification when it finishes, regardless of whether it fails or succeeds. This ensures that the job ran successfully.

3. DBCC CHECKDB

DBCC CHECKDB is a thorough way of checking the entire database for corruptions. This check should be scheduled to run during a maintenance window, either daily or weekly depending on the system’s resource capacity. To run DBCC CHECKDB, use the following syntax:

DBCC CHECKDB(<database name>);

When corruptions are detected, the job may succeed with information in the output or fail with an error message. It is recommended to send the job output to a text file and have the job send the file via email, regardless of the job’s success or failure.

4. Alerts

DBAs should set up alerts to be notified of any critical errors in a database, including corruption errors. There are various scripts available to set up these alerts, such as the one provided by Glenn Berry at this link: https://www.sqlskills.com/blogs/glenn/creating-sql-server-agent-alerts-for-critical-errors/

Conclusion

Database corruptions can be a major headache for DBAs, but by implementing the methods discussed in this article, you can detect corruptions early and take appropriate actions. Remember to always have good backups available and stay vigilant in monitoring your databases for any signs of corruption. Good luck!