SQL Server Database is the storage unit file of the SQL Server which contains information in the form of pages. The database contains MDF (primary), NDF (secondary) and LDF (log) files. These files have information within the pages and pages are stored at some location. Being an SQL Server administrator, you need to be careful of all types of corruption in the SQL database. But, it is not possible always. There is a constant risk to your SQL Database and you may encounter a SQL page level corruption error like this –
This is basically a SQL Server page level corruption error which needs to be rectified in order to access the SQL Server data. The problem is to be solved at page level, i.e. one by one and not for the entire file. So, it becomes easier to perform the solution manually. Before going for any particular solution for the corruption errors, let us look at some causes for this page level corruption error.
Reasons for SQL Server database Page Level Corruptions
- Fault in Hardware – It is the prominent cause for this SQL Server page level corruption. The hardware malfunction leads to the corruption in SQL easily.
- Irregulation in Power Supply – If while the SQL database is running and the power supply is interrupted due to some faults, it will causes corruption or errors in SQL pages.
- Wrong updates installation – It is when while you are trying to update the SQL Server with the current version but fails to choose the correct one. In place of updating the SQL Server, it damages the pages with bugs.
- Virus attack or infections with new software – Upon installing any new software on the system, there is vulnerability that it could carry bugs, viruses with it. Such viruses can infect the SQL Server as well while corrupting the SQL Database pages.
These important reasons for the error could be taken care by deploying these solutions:
- Ensure protective hardware by periodic checkups and maintenance
- Checking and correcting power supply from time to time
- Using authentic and licensed update for the SQL Server will help you avoid the corruption issues
- Finally, having updated anti-virus software will not let other software harm your SQL Server
So, these are the preventive measures to avoid SQL database page level corruption. But, what to do once you get an error like that mentioned above in the image? The answer is using the in-built DBCC CHECKDB command.
Note: Before jumping to any method for solution, you must have a backup copy of your SQL Server database saved at some other location.
Fixing SQL Database Page Level Corruption manually
If you have a full recent backup of the SQL Server database, then you can try performing the restore of this backup file while keeping the database online (if possible) and executing the following database restore query.
In the above command, provide the SQL Server database name and the backup file path location in place of the mentioned brackets.
It is possible that you are unable to run restore the online database. So, you can follow the next procedure to fix the issue. The manual way which you can use to fix the SQL Server database page level corruption is using DBCC CHECKDB command. For the effective solution, you must have the following arrangements:
Step 1. Have a text comparison tool allowing checking the differences between original and corrupted file
Step 2. Run DBCC CHECKDB command on the corrupted file. It will check the database file location where problem actually originates. Also, it uses minimum sources for data repairing.
Step 3. Switch on the trace flag 3604 – run DBCC TRACEON command. Now run DBCC PAGE command. It will examine infected page content.
- DBCC TRACEON (3604)
- DBCC PAGE ({ ‘dbname’ | dbid}, filenum, pagenum [, printopt={0|1|2|3}])
- Here, filenum and pagenum are page ids related to different system tables.
Other parameters are defined below:
- the header part of the page will be printed–“0”
- SQL page header with per row hex dumps–“1”
- Detailed page header with a full hex dump of the page–“2”
- Description of per row interpretation of page header–“3”
Step 4. Confirm the page number using command ‘SELECT * from dbo.tablename’.
Step 5. With the known location of where you got the corruption, compare it with original page using text comparison tool.
Step 6. This is the final step. Fix the pages once again using above steps and run DBCC CHECKDB command on your restored file. If correctly executed and level of corruption is less, you have overcome SQL Server page level corruption.
Challenges in manual method
- Need enough technical knowledge of SQL Server scripts and commands to run SQL commands without any flaw.
- Time consuming process with a lot of concentration and technical skills required
- I/O errors will occur because direct editing makes the file vulnerable to faults. Failure in correct copy/paste of checksums on pages leads to failure in opening the database too
- If the area and level of corruption is more, it will automatically damage other pages as well
Try Professional Solution
Through above limitations or challenges in manual way, it is confirmed that this way is completely unreliable for page level corruption in SQL Server. So, the best recommendation is using third-party SQL recovery tool. The tool is all about fixing SQL based corruptions and errors at a one go flawlessly. Get a deep knowledge and understanding of the software for free by working on the free trial version of the tool which you can download directly from the software website.
Concluding lines
The article has discussed about the SQL Server database page level corruption, its reasons, manual solution & its limitations, a better approach to fix the error. All these sum up and test the intelligence level of the user. It is definitely up to him that what method he would like to go with. Experience with your decision!
