Database corruption in SQL Server, a Relational Database Management System, can pose a major threat to organizations’ functioning due to data loss and system downtime. Its prompt detection and debugging are necessary to ensure business continuity, optimized performance, data integrity, and the reputation of the organizations, Moreover; it also saves businesses from facing big financial losses.
While many manual methods can help to find corruption and repair SQL database server, carrying out SQL recovery with the help of a third-party tool would be a better move. Stellar Repair for MS SQL is reputed software to consider in this regard.
Here, we have discussed some crucial methods to Check database corruption in SQL Server followed by satisfying SQL recovery. First, let’s talk about the reasons leading to SQL Server database corruption.
Common causes behind SQL corruption
Despite being reliable and robust, MS SQL Server is still prone to being affected by several threats, eventually leading to errors and corruption. Many causes can be responsible for this situation. Knowing about them is critical to troubleshoot and mitigate the issues. Below are some of the likely reasons for SQL database corruption
1. Hardware Failures
In most instances, database corruption occurs due to hardware issues. Malfunctioning of hardware components like CPUs, hard disks, memory units, controllers, etc., can disturb the data integrity, hence leading to corruption issues.
2. Problem in Memory Disk Subsystem
Failure of subsystems that take care of disk and memory storage can lead to improper arrangement of table details. This bars the users from accessing useful data from the database, giving way to corruption.
3. Anti-Spyware and Anti-Malware
These programs are crucial to maintaining system security but can prove harmful to SQL Server databases. During scanning, they may interfere with database files, leading to corruption. Excluding database files from routine anti-malware scans will prevent this interference.
4. Software Bugs
Bugs within the SQL Server software or in the applications interacting with SQL Server can cause data inconsistencies, which is a clear indication of corruption.
5. Database compression
Admins often compress the database to save disk space or to enhance I/O performance. Nevertheless, this may increase the chances of corruption, as the method to compress regular files would not be suitable to compress a database. In case compression is inevitable, it would be advisable to use the inbuilt SQL Server backup and data compression tools.
Knowledge about these reasons will be helpful in finding and resolving corruption at an early stage.
Check database corruption in SQL Server
Let’s now move forward to know about methods to Check database corruption in SQL Server.
1. Check SQL Server bad pages
A corrupted SQL Server does not prompt any error notification. The user has to find the corruption manually by checking the suspect pages by running this query:
SELECT * FROM msdb..suspect_pages
This method is better as compared to checking the entire database for corruption. If the result does not show any row, it means the query did not find any corrupted instance. However, this query checks only the entries that went corrupt earlier. It will not check a new entry for corruption. Hence, despite no error reported, you cannot rule out the possibility of database corruption.
2. Run DBCC CHECKDB
DBCC CHECKDB is a useful command to check database integrity and find database corruption in SQL Server. You can run the following command:
DBCC CHECKDB ‘Your_db_name’;
If the query finds corruption, it will show consistency errors along with complete details citing the reason behind corruption.
You may use several other options with DBCC to check database corruption, such as:
- DBCC CHECKCATALOG
- DBCC CHECKTABLE
- DBCC CHECKALLOC
3. Use CHECKSUM
PAGE_VERIFY option or the checksum is a crucial method to find page-level corruption because of I/O errors.
In the databases on SQL Server 2008 and later versions, you will find CHECKSUM as the default Page verification setting.
However, In SQL Server 2005, the PAGE_VERIFY option might be set to TORN PAGE DETECTION or NONE.
In the latter case, you can modify the PAGE_VERIFY option to CHECKSUM by following these simple steps:
- Open SQL Server Management Studio (SSMS)
- Select the database from the Object Explorer in the left panel
- Right-click the selected database and click Properties
- In the Database Properties window, click Options
- Go down to Recovery section
- Look for PAGE_VERIFY and click the dropdown corresponding to it
- Select CHECKSUM and click OK.
Alternatively, you may also execute the T-SQL command as given below for this purpose:
ALTER DATABASE SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;
4. Set up SQL Server Agent Alerts
Setting an alert with SQL Server Agent is crucial, as it will notify you about the possible corruption so that you can resolve it on time and avert any dire situation. Here are the steps for the same.
In SQL Server 2012 or later versions, you can use the Memory Error Recovery feature to alert you about the error. Additionally, it will also clean the database pages if it finds them corrupt. This feature will work better with an advanced processor. To check for this feature, search the SQL Server Log for the following message:
Machine supports memory error recovery. SQL memory protection is enabled to recover from memory corruption.
This feature can help you get away with minor database corruption issues in SQL Server.
Let’s learn the steps to set SQL Server Agent Alerts so that you can know about a possible corruption:
- Open SQL Server Management Studio à SQL Server Agent
- Right-click SQL Server Agent à Properties
- In the Properties page, click Alert System under Select a page
- In the Alert System page on the right side, tick the Enable Mail Profile checkbox
- From the Mail system list, select Database Mail
- Click OK to configure mail for SQL Server
- Next, expand SQL Server Agent à Right-click Alerts à New Alert
- In the New Alert page, mention the name for the alert and other details
- In the Response page on the left pane of the New Alert window, click Response
- Tick the Notify Operators checkbox
- Choose an existing operator or create a new one
- Tick the E-mail checkbox
- Click OK.
These easy steps will create an alert. Eventually, in case of corruption, the DBAs will get an alert email so that you can take the right action at the right time.
Manual Resolution of the Database Corruption Issue
Resolving the SQL database corruption is possible by executing several SQL commands. To start with, it is always advisable to restore the database from a recent healthy backup. In the absence of a backup, you may use the DBCC CHECKDB command in combination with several repair options to repair the SQL database server.
1. REPAIR REBUILD
DBCC CHECKDB (‘Your_db_name’, REPAIR_REBUILD);
This option can resolve minor corruptions in a SQL Server database without the risk of data loss.
2. REPAIR_ALLOW_DATA_LOSS
DBCC CHECKDB (‘Your_db_name’, REPAIR_ALLOW_DATA_LOSS);
This option can fix a severely corrupted database. In fact, it can resolve all the errors listed in a DBCC CHECKDB operation, but it may lead to data loss. Therefore, it is better to use it only if you have no other option left.
3. REPAIR_FAST
DBCC CHECKDB (‘Your_db_name’, REPAIR_FAST);
This option will deal with the database corruption quickly, and enable you to resume working on the database at the earliest. It will however fix only minor corruptions and skin severely corrupted data files untreated.
These repair methods work actively but accompany several risks. This further necessitates the need for a third-party tool such as Stellar Repair for MS SQL to ensure complete SQL recovery.
Stellar Repair for MS SQL – A Comparably Better Solution for SQL Recovery
After using one or more of the aforementioned methods to check database corruption in SQL Server, your next move would be to fix the issue. While manual methods are error-prone, third party software can deal with corruption more conveniently, resulting in hassle-free SQL recovery. It is beyond doubt a better method to repair SQL database server owing to numerous reasons:
- Repairs damaged NDF and MDF files
- Ideal to repair DBCC CHECKDB consistency errors
- Retrieves deleted records from SQL Server Database
- Restores MSSQL Database from .bak File
- Offers numerous options for file saving
- Lets you preview repairable MS SQL Database Objects
- Can resolve all types of SQL database corruption issues
You may download the free trial version to get an idea of its working. The trial version will however let you only scan and preview the recoverable SQL server database objects. To get complete functionality, feel free to buy the paid version of Stellar Repair for MS SQL.
Conclusion
Several reasons may lead to corruption in SQL Server databases. Knowing about them on time will help in their prompt resolution. To check database corruption, you can run the DBCC CHECKDB command, check SQL Server suspect pages, use CHECKSUM, or set up SQL Server Agent Alerts.
After knowing about database corruption in SQL Server through any of these methods, it is time to resolve them.
Using different options with DBCC CHECKDB is a widely used solution but it could lead to several errors. A comparably better move would be to use Stellar Repair for MS SQL.
This third-party SQL recovery tool will repair SQL database server for all the errors, ensuring a corruption free database. You can use a trial version of this software or buy a paid version. Once you have it, you can rest assured of hassle-free database operations.
Mohammad Atif is a talented writer with a passion for creative expression and diverse writing skills. Possessing a background in technology and a Master’s degree in Computer Applications (MCA), he initially embarked on a career as a Software Developer. However, his natural inclination towards writing eventually led him to discover his true calling in the writing profession.