DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. SQL Server Database Corruption Detection

SQL Server Database Corruption Detection

In this article, take a look at SQL Server database corruption detection.

Daniel Calbimonte user avatar by
Daniel Calbimonte
·
Aug. 06, 20 · Tutorial
Like (2)
Save
Tweet
Share
5.09K Views

Join the DZone community and get the full member experience.

Join For Free

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:

Java
 




x


 
1
ALTER DATABASE <database name>
2
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:

Java
 




xxxxxxxxxx
1


 
1
BACKUP DATABASE AdventureWorks
2
TO DISK='X:\SQLServerBackups\AdventureWorks.bak'
3
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:

SQL
 




xxxxxxxxxx
1


 
1
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.:

Java
 




xxxxxxxxxx
1


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

          
3
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.

Database sql

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Iptables Basic Commands for Novice
  • OpenID Connect Flows
  • Distributed SQL: An Alternative to Database Sharding
  • Understanding gRPC Concepts, Use Cases, and Best Practices

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: