How to Check / Detect Database Corruption in SQL Server – Speedly
SQL Server Database corruptions, DBAs can understand how painful it is. Well does not occur so frequently but what if happens, it is always the troublesome and stressful situation for organizations and to there DBAs. How to check database corruption in SQL Server? Or How to detect if it occurs?
Causes of Corruption in SQL Server Database
Microsoft SQL Server Database is the core part to run the business. It is the relational database management system (RDBMS) developed by Microsoft for small & big organizations, home users, etc. to securely store their crucial professional data.
Corruption can occur due to many of the reasons like caused by hardware failure or subsystem issues, sudden power cut, SQL Server stops responding, while performing the Delete operation, etc. Being DBAs, there is not much to prevent these corruptions, only one can try to detect and resolve it as early as possible. Here you will find out how to check if the SQL Server database is corrupted and fix it using the relevant solution.
Detect / Check Database Corruption SQL Server using Four Methods
Following things, DBAs can do to find out corruption in SQL Database. This section only covers to the method to detect corruption.
-PAGE Verify Option – Recovery
-Backup with Checksum
-DBCC CHECKDB
-Create an ALERT
Note: DBCC CheckDB command lets DBA to check database corruption in SQL Server, but corruption in SQL compact edition is verified with Engine object.
Method 1: How PAGE Verify Option detect corruption?
On each Database created within SQL Server, Page Verify Option. The user can set it by using T-SQL script or by SSMS. Whenever read & write operation is performed on a page, this option tells the database how the health of the page is verified during the process.
Set with SQL Server Management Studio (SSMS)
1. Click on Database you need to check, and select properties.
2. Next in the Database Properties window. Select Options page and drag down to Recovery.
3. Under Recovery option, there is Page Verify. In this click on down arrow option and Select CHECKSUM and click on OK button. As shown in below image.
Set using T-SQL
ALTER DATABASE
SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;
DBA can use three options i.e. ‘NONE’ to not verify the page, ‘TORN_PAGE_DETECTION’ was used earlier and is never the best option to choose for SQL 2005 & above, and the best option to choose is ‘CHECKSUM’
Method 2: Using Database Backup with CHECKSUM
Run the SQL database command with CHECKSUM always. This means when the backup is performed, SQL Server Check pages during the backup process and if it detects corruption in any page. In case, found any corruption backup fails.
If the job is set, when a backup fails, DBA will receive backup fail notification on their email.
Let’s see demo how command looks:
BACKUP DATABASE abc
TO DISK=’D:\SQLBackups\abc.bak’
WITH CHECKSUM;
Method 3: DBCC CHECKDB Command
Using DBCC CHECKDB command user or DB can the check the logical and physical integrity of all the Data objects within the database selected. This is used for checking full the database. It is recommended to run DBCC CHECKDB weekly or one can run more frequently depending on your system capacity.
Run: DBCC CHECKDB(your database name)
If detects any corruption, the job gets failed with an error message. This error message shows complete detail why job gets failed or reason why database corruption in SQL Server occurred.
Method 4: Create an Alert
DBAs can create an Alert script and get informed to themselves if any critical error occurred in the database.
What to do if Corruption in SQL Server Detected?
If Database corruption in SQL Server detected, best is to restore it from most recent backup available. What to do if no recent backup available? In this case, DBAs can perform below manual method that may help to repair.
1. DBCC CHECKDB REPAIR_REBUILD
Repair_REBUILD is the most secure manual approach used to repair the corrupted database. Well, this helps if the corruption level is not so high.
2. DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS
REPAIR_ALLOW_DATA_LOSS detects the corrupted part of database file selected and then it completely removes that part. Mostly, not recommended to perform because it may result from huge data loss.
Important Note: The user can also read another post to know how often should I run DBCC CHECKDB to repair the corrupt SQL database.
Thus, using an authentic solution is the best way to repair and recover corrupted SQL Database. DBAs can try SQL Database Repair Tool to fix corruption and recover the database without any data loss.
Features of Alternate Solution to Detect & Repair Database Corruption
A One-stop solution is to use SQL Database Recovery software to resolve how to check database corruption in SQL server and repair if detected. Why choose this software? The tool is available with most demanded features to check database corruption SQL Server and quickly fixes without any data loss.
Remove corruption from both MDF & NDF files and recover all items like tables, triggers, indexes, stored procedure, etc. Capable to preview and recover deleted SQL database records. Provides user-friendly interface.
Conclusion
Above in this article, we have elaborated “how to check database corruption in SQL Server?”. Once the process to check database completes, and DBAs detect any corruption then he needs to decide the correct method to get rid of corruption. If no recent backup is available then the best approach is to try an alternate solution.