Summary: This blog discusses methods to check database corruption in SQL Server. It also suggests solutions you can use to fix a corrupted database manually. And, it explains how using a third-party tool, Stellar Repair for MS SQL, can help repair a corrupted database and restore it without any data loss.
Your database may be healthy, but it can become inaccessible or corrupted anytime due to events like server crash, an issue with your hardware or software, virus or malware attack, etc. If you’re lucky enough, you may receive an error message indicating database corruption in SQL Server. However, you might fail to identify corruption sometimes, until you can no longer access your database. That’s why, as administrators, it is crucial to detect corruption in a SQL Server database as early as possible.
What You Can Do to Check SQL Server Database Corruption?
SQL Server has built-in mechanisms you can use to detect a corruption issue:
Method 1 – Monitor SQL Server Suspect Pages
Querying the system table (i.e., suspect_pages), residing in the msdb database of SQL Server, is the first thing you should try instead of checking an entire database for corruption.
Monitoring the suspect_pages table will help you quickly find any corrupted pages in your database. To check for bad pages in the ‘suspect_pages’ table, run this query:
| SELECT * FROM msdb..suspect_pages |
For detailed information on how to monitor SQL Server suspect_pages, read this: How to Monitor SQL Database Suspect Pages?
Method 2 – Use PAGE Verify Option
CHECKSUM, used as the ‘PAGE_VERIFY’ option on your database can help detect page level corruption caused due to I/O errors.
Although databases on SQL Server 2008 and higher versions use ‘CHECKSUM’ as page verification setting by default, this wasn’t the case with SQL Server 2005 instances. While checking databases migrated from an earlier server instance, you might find the PAGE_VERIFY option set to TORN PAGE DETECTION or NONE.
You can change the database option to CHECKSUM using SQL Server Management Studio (SSMS) or T-SQL.
Changing PAGE_VERIFY Option to CHECKSUM using SSMS
1. In SSMS, right-click on the database you need to check for corruption and click Properties.
2. On the ‘Database Properties’ window, click Options. When the ‘Options’ page opens, scroll down to Recovery. Next, click the drop-down next to Page Verify, choose CHECKSUM, and then click OK.
Changing PAGE_VERIFY Option to CHECKSUM using T-SQL
Execute the following T-SQL commands to set ‘PAGE VERIFY CHECKSUM’ option:
| ALTER DATABASE SET PAGE_VERIFY CHECKSUM WITH NO_WAIT; |
Method 3 – Run DBCC CHECKDB
Running DBCC CHECKDB regularly to check for database integrity is crucial for detecting database corruption in SQL Server.
| DBCC CHECKDB ‘database_name’; |
If it finds corruption, it will return consistency errors along with an error message showing complete details why database corruption in SQL Server occurred.
Method 4 – Set up SQL Server Agent Alerts
While running DBCC CHECKDB commands can help check integrity of the databases, without setting up an alert, you may not notice corruption for days or even weeks. But, setting up a quick alert with SQL Server Agent can help you know of possible SQL database corruption before it’s too late to fix the problem.
Steps to Set up SQL Server Agent Alerts
- In SSMA, right-click on SQL Server Agent, and click on Properties.
- On your database properties page, click Alert System under ‘Select a page’. In the ‘Alert Systems’ page, check the Enable mail profile checkbox, and choose Database Mail from the Mail system list.
- Click OK to configure mail for SQL Server Agent.
- Now to create alerts for the SQL Agent, expand SQL Server Agent, right-click on Alerts folder, and then select New Alert.
- Specify the name of the alert, for instance ‘Corruption Suspected’ (or anything you like), leave the Database name as <all databases>. Next, from the Severity drop-down list, choose “023 –Fatal Error: Database Integrity Suspect”.
- Click on the Response tab under ‘Select a page’. From the ‘Response’ page do the following:
- Check the Notify operators checkbox.
- Choose an existing operator or create a new one with an assigned email address.
- Check the e-mail checkbox.
- Click OK.
After performing these steps, an alert gets created. It will send an email to the DBAs notifying them about corruption when a database integrity event happens.
Note: While you may perform checks to detect database corruption, checking database backups for corruption is equally important. Doing so will ensure that you’ve a good backup copy to restore to in case of unexpected database downtime. Here’s a detailed blog on how to identify corrupted SQL backup file: How to Identify Corrupted SQL Backup File.
What to do if SQL Server Database is Corrupted?
If you’ve detected corruption in your SQL Server, the most effective and recommended solution is to restore it from a good known backup available. If you don’t have backup, running DBCC CHECKDB with the following repair commands can help fix the corrupted database:
- REPAIR_REBUILD
To repair a corrupted SQL database without data loss risk, you can try running “DBCC CHECKDB with Repair_REBUILD”:
| DBCC CHECKDB (‘Database_name’, REPAIR_REBUILD); |
The ‘REPAIR_REBUILD’ option can help fix minor corruption in a SQL database. For a severely corrupted db, you will need to use ‘REPAIR_ALLOW_DATA_LOSS’.
- REPAIR_ALLOW_DATA_LOSS
While this repair option can fix all types of errors reported by DBCC CHECKDB, it may cause data loss. That’s why, it is recommended as the last resort to fix database consistency errors. To run “DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS”, execute the following command:
| DBCC CHECKDB (‘Database_name’, REPAIR_ALLOW_DATA_LOSS); |
Is There a Better Alternative to Repairing a Corrupted SQL Database?
Using a specialized SQL database repair tool is a better alternative to repair corrupt SQL database. Trusted by MVPs and administrators, Stellar Repair for MS SQL is the best solution to fix corruption and restore the database without any data loss. It repairs corrupt MDF and NDF files and recovers all the objects like tables, deleted records, stored procedures, indexes, triggers, etc.
The software can be easily downloaded and installed within a few seconds. It can run on a Windows, Ubuntu, CentOS 7, and Red Hat Enterprise Linux 7 OS.
Check out this video to understand how to repair a corrupt SQL database with Stellar Repair for MS SQL software:
Conclusion
SQL Server crash, I/O subsystem failure, virus or malware intrusion, and bugs in SQL software are some common reasons that could lead to database corruption. You may not know if a database has turned corrupt until it becomes inaccessible. Fortunately, you can detect a corrupted database by using the methods discussed in this blog.
In case, your database turns corrupt, try restoring the database from an updated backup. If backup is not available, apart from repairing the database using “REPAIR_ALLOW_DATA_LOSS”, there’s not a reliable solution to fix SQL Server database corruption. Also, it involves data loss risk. But, using a third-party SQL database repair software like Stellar Repair for MS SQL can help you restore your database and all its data with integrity and precision.
