Summary: As a database administrator, you can’t do much to prevent corruption in SQL database. However, you can try to check the database for corruption to fix the issue as soon as possible and prevent data loss. In this post, we’ll discuss different methods to check database corruption in SQL Server. We will also mention an advanced SQL database repair tool that can help you to quickly repair the corrupt database without any data loss.
MS SQL Server stores all the objects, such as tables, views, functions, stored procedures, triggers, etc. in database files. Like other database files, MS SQL Server database files are also prone to corruption and integrity issues. Corruption in SQL databases usually occurs due to issues with hardware, such as faults in hard drive, CPU, memory unit, etc. Sometimes, interference with third-party or incompatible software and virus/malware infection can cause corruption in the database.
When the database gets corrupt, you may receive errors when opening the database file or even fail to access the database. But this is not always the case. In some instances, you may not get any error or warning message regarding corruption in the database. So, if you’re facing any issues with your database or suspect corruption in the database file, then you will immediately check the database file for corruption. In this post, we will be explaining some methods with stepwise instructions to check database corruption in SQL Server.
Methods to Check SQL Server Database Corruption
There are some utilities in MS SQL Server that can help you detect corruption in the database. Let’s see how to use these utilities to check corruption in SQL Server database.
Method 1 – Check the Suspect Pages Table
MS SQL Server maintains logs of the suspect pages in suspect_pages table in msdb database. The dbo_suspect_pages table contains all the corrupt pages (with a limit of 1,000 rows) reported by SQL queries. You can monitor the suspect_pages table to quickly detect any corrupted pages in your database. To check for corrupt pages in the suspect_pages table, run this query:
SELECT * FROM msdb..suspect_pagesNote: This query only checks those entries which were corrupted earlier. It will not check the newer entries.
Method 2 – Use PAGE Verify Option
If CHECKSUM is enabled in the PAGE_VERIFY option, whenever a page is written to disk, SQL Server database engine calculates CHECKSUM across the entire page and stores the value in the page header. When the page is read back, the CHECKSUM is recalculated and compared with the stored value. If there is a mismatch, the server throws error. This indicates corruption in the page.
You can use the PAGE_VERIFY option to detect page level corruption in the database. To do so, run the following query:
SELECT name, page_verify_option_desc FROM sys.databasesMethod 3 – Run DBCC CHECKDB
You can run the DBCC CHECKDB command to check the overall integrity of the database. It checks inconsistencies in the database and also checks corruption in tables, data pages, and indexes. To check SQL database corruption, run the following command:
DBCC CHECKDB ‘database_name’;If it finds corruption, it will return consistency errors, along with error message with description and also recommend the repair option to fix the issues.
Method 4 – Create SQL Server Agent Alerts
You can create different types of alerts using the SQL Server Agent Alerts to get notified when there is some critical condition or occurrence that can cause corruption or damage in SQL database. SQL Server generates events that are stored in the Windows application log. This application log is read by the SQL Server Agent that compares the events written in the log with the alerts you have created. When there is a mismatch, then SQL Server Agent sends an alert – an automated response to an action or event. This way you can get alerted, if there is corruption or any other issue in the database.
Here are the steps to set up SQL Server Agent Alerts:
- Open SQL Server Management Studio (SSMS), right-click on SQL Server Agent, and then click on Properties.
- On the properties page, click Alert System under Select a page.
- On the ‘Alert Systems’ page, check the Enable mail profile checkbox and choose Database Mail from the Mail system list.
- In the Mail profile list, choose a mail profile.
- Click OK to configure mail for SQL Server Agent.
- Now, to create alerts, expand SQL Server Agent, right-click on the Alerts folder, and then select New Alert.
- Specify the name of the alert. For instance, ‘Corruption Suspected’ (or anything you like). Then, leave the Database name as <all databases>. Next, from the Severity dropdown 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.
What to do if SQL Server Database is Corrupted?
If corruption is detected in your SQL Server database, the easiest and recommended solution is to restore the database from a good known backup. If you don’t have a backup, then follow the below the methods to repair the corrupt database file.
1. Repair Database using DBCC CHECKDB Command
You can use the DBCC CHECKDB command to repair the corrupted database. To repair the database, first set it to the SINGLE_USER mode to prevent other users from changing the data during the repair process. To do this, run the following command:
ALTER DATABASE databasename SET SINGLE_USERYou can run the DBCC CHECKDB command with REPAIR_REBUILD to repair the corrupted SQL database without data loss risk. Here’s the command:
DBCC CHECKDB (‘Database_name’, REPAIR_REBUILD);The ‘REPAIR_REBUILD’ option can repair indexes, including non-clustered indexes. This can help fix minor corruption in the database.
To rebuild the database quickly, you can use the REPAIR_FAST option with the command. However, it only maintains syntax for backward compatibility. Here’s the command:
DBCC CHECKDB (' Database_name', REPAIR_FAST)GOIf the database is severely corrupted, then you can run the DBCC CHECKDB command with REPAIR_ALLOW_DATA_LOSS. Here’s the command:
DBCC CHECKDB (‘Database_name’, REPAIR_ALLOW_DATA_LOSS);This repair option can fix all types of errors reported by DBCC CHECKDB, but it may cause data loss. That’s why, it is recommended as the last resort to fix database consistency errors.
2. Repair Corrupted Database using a Professional SQL Repair Software
To repair the corrupted database quickly and without any data loss, then you can use a specialized SQL database repair tool, like Stellar Repair for SQL. This MVP-recommended tool can repair both MDF and NDF files. It can recover all the objects, like tables, deleted records, etc., from the damaged database and save them to a new MDF file with complete integrity. It can help you fix all types of corruption issues in the database. The software is easy to download and install. It can run on both Windows and Linux systems (Ubuntu, CentOS 7, and Red Hat Enterprise Linux 7 OS).
Conclusion
There are various reasons that could lead to corruption in SQL database. You may not know if a database has turned corrupt until it becomes inaccessible or throws errors. If you suspect that your database is corrupt, then it is important to check the database for corruption. You can follow the methods discussed in this post to detect corruption in the database.
If the database is corrupted, you can try to repair it using the DBCC CHECKDB command but it can cause data loss. Alternatively, you can use a professional SQL repair tool, like Stellar Repair for MS SQL to repair the database with complete precision. The tool can repair the SQL database of any size and SQL server version. It supports MS SQL Server 2022 and earlier versions.
