How to Check SQL Database Corruption With Top Techniques
Despite the advantages of this application, the databases also prone to various types of corruption problems. Due to which the MDF file data become inaccessible. That is why we came with this article. Here, we are going to discuss causes for SQL Server database corruption as well. Along with this, we will illustrate how to check SQL Database Corruption & method to secure the data from damage.
Microsoft SQL Server is a well-known name in the domain of RDBMS and provides great efficiency. Due to which, it is used by professionals as well as Organizations to manage the sensitive database in proficient manner. Adding to it, the self-explanatory interface and advanced features of MS SQL Server makes it best in its arena.
But, Corruption Demon is Always There!!!
Reasons for Database Corruption in SQL Server 2019 / 2017 / 2016
As we said above, like other databases, SQL Server is also vulnerable to corruption. This situation ultimately leads to different types of errors. Generally, most of the issues arise in SQL Server is due to the problem in hardware infrastructure like hard-disk failure, damage in memory unit and so on. Besides this, we below mentioned some prime reasons for corruption. So, you can check table corruption in SQL Server database.
- Damaged Disk
It might be possible that there could be damage in disk’s subsystem at which the data of SQL Server stored. Due to this reason, poor management of database table takes place. As a result, the user does not get appropriate results. - Viruses
The introduction of malicious virus in the application also cause severe damage in MDF & NDF files. This is a common cause of corruption in databases across globe as cyber-attacks are getting more common these days. - Lack of Solutions
As we all know, the antivirus software is considered to be crucial component of security planning. But, the software like anti-malware is not beneficial for SQL Server and creates files inaccessibility type of problem. Therefore, it is suggested to stay away the SQL Server files from antivirus software. - Mismanagement
Last but not least, there are various folks that reduces the size of database by compression technique to make new memory space. However, the SQL data files are never meant for file compression purpose. This process enhances the chances of corruption. In fact, the users also use to compress the backup file. Beware, if you make the backup file more compact, then there will be high chances of corruption. So, whenever you try to compress SQL database, then take the help of inbuilt tool present in SQL Server for compression instead of using untrusted third party apps.
After understanding the types of SQL database corruption, let’s find out how to identify or check Database Corruption in SQL Server.
How to Identify or Check SQL Database Corruption?
No matter, the SQL database (MDF & NDF) files are minor or major corrupted. The result always harms the users. Hence, it is important to know the level of corruption in database before it becomes giant. So, whenever the database shows abnormal behavior, just follow these instructions and check SQL table corruption.
1. The Basic Method
Simply need to place and execute the following query to check the bad pages.
SELECT * From msdb.dbo.suspect pages
The query results in no error in case of zero corruption in SQL database. It means no page has corruption. However, this result is not always correct. Because this query checks the corrupted entries of old database. In such a situation, if there is any corruption available in newer database, then it will not be identified by this command. For this, you can use the second workaround and thoroughly detect the undetected issues.
2. DBCC CHECKDB Method
Users often struggle with DBCC CHECKDB job failed issue. However, it is a very popular command used by database administrators to find out the logical as well as physical integrity of all the objects. Plus, this inbuilt function also comprises other commands such as DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG. To check the database table consistency, run the following command and get the result on SSMS panel.
DBCC CHECKDB (Name_of_database)
3. Set SQL Server Agent Alert
The Agent Alert method is quite helpful for users. This way, they can get quick alert at the moment something goes fishy. Let’s have a look at the steps of how to set up Agent Alerts.
- Go to SSMS, Right Click SQL Server Agent & then Select Properties.
- Click on the Alert System option to move further with the process.
- Click the Enable mail profile checklist & then Select Database mail.
- Hit the OK button to configure your mail for SQL Server.
- Go to the Alerts folder & then Select the New Alert option.
- Specify the name of the Alert that makes sense with its task.
- In Select a Page, Select the Response tab & check to notify operators.
- Select Operator, Click Email Checkbox & then Hit the OK button.
4. Page Verify & CHECKSUM Option
In order to detect page-level corruption, users can also rely on the Page_Verify option. It can detect the issues that occur due to the I/O errors.
We all know that SQL Server 2008, 2008 R2, & above versions use CHECKSUM as default for page verification. But it’s not like this in older 2005 & other versions. When users chcek the databases migrated from an earlier instance of SQL Server, there will be a TORN PAGE DETECTION or NONE option for Page-Verify.
To know how to check database corruption in SQL Server & change this setting back to CHECKSUM, simply follow the below steps:
- Open SQL Server Management Studio
- Now, Right Click the desired Database
- Go to Database properties & Click on Options
- Go to Recovery tab & Open “Page Recovery” drop-down
- Change the Page_Verify option with the CHECKSUM option
- Run the below command:
ALTER DATABASE SET PAGE_VERIFY CHECKSUM WITH NO_WAIT;
Manual Solution to Fix the Database Corruption Issues
Now, let’s have a look at the manual solution first that users asks the most. Moreover, there are some risks associated with the manual method that users are unaware of. Evidently, we will be discussing them as well to get a comprehensive understanding.
1. DBCC CHECKDB Repair Option
As we can use this command for checking corruption, we can also take this inuse to resolve the corruption issues. There are a total of three ways in which users can use this command. Let’s understand in detail
Repair_Allow_Data_Data_Loss
Use the below command to if users want to repair the corruption but it comes with a catch. Users might loose some of their crucial data files as this solution allows the loss of damaged data files.
DBCC CHECKDB (‘Database_name’, REPAIR_ALLOW_DATA_LOSS);
But this one can repair errors in the database of almost all types. Now, you know what you have to do & at what cost.
Repair_Fast
The repair fast command is for users who are in search for a quick solution, This way users can fix the corruption issues quickly & can get back to work as soon as possible. But this one do not repair heavily damaged data files & skips them. Follow the below command:
DBCC CHECKDB (‘Database_name’, REPAIR_FAST);
Repair_Rebuild
Now, if users want to perform a in-depth cleaning of corrupt data files of SQL, they can select this method right away. It is time-consuming but the results are balanced. It performs a deep repair & solves a variety of errors. Below is the command mentioned for this one.
DBCC CHECKDB (‘Database_name’, REPAIR_REBUILD);
How to Protect SQL Database from Corruption?
Once you check Database Corruption in SQL Server and find out there is a problem in the database, take the help of Best SQL Recovery Tool to fix it. It works is to restore the objects such as Tables, Stored Procedures, Triggers, Views from corrupted multiple MDF & NDF files.
You can also export the recovered data directly into SQL Server environment or SQL Server Compatible Scripts without losing any database structure. Also, it can scan corrupt database file of any SQL Server editions including SQL Server 2019, 2017, 2016, 2014, 2012 or below versions. So, just check SQL database corruption and repair it as soon as possible before its getting too late.
The Final Say
Finally, we can move further & say that we know how to check database corruption in SQL Server with ease. Following the above-mentioned guide along with the solutions can help users get the perfect solution. The automated solution is way better than the other methods.
Frequently Asked Questions-
Q-1. What’s the step-by-step solution to check SQL database corruption & fix it?
Ans: Download the tool & follow the below steps:
- Open Tool & Add MDF Data Files.
- Select the Quick or Advanced Scan.
- Preview all the database files here.
- Set the destination location for files.
- Click on Export to get data after repair.
Ans: To identify the damaged SQL pages, run the following command-
DBCC TRACEON (3604)
DBCC PAGE ( {‘db_name’ | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])
Ans: Multiple reasons are responsible for SQL database corruption – Hardware failure , Software failure, Virus introduction.
Ans: You can take the help of SQL Recovery software to fix corrupt MDF & NDF file.