Summary: This article is going to help users learn how to repair suspect pages in SQL Server? Moreover, there are both manual & automated ways mentioned for users to get complete knowledge about the entire topic. Last but not least, the reasons for this task are also mentioned below to easily find the root cause as well.
SQL Server database is indeed the most refined RDBMS available. It has many states out of which there is one we call SUSPECT state. Let’s understand it & know what other states are available here.
When the database cannot be recovered while starting up, it gives an alert that the database is in SUSPECT Mode. It means something is not going as it should be. Basically, it is a soft signal that there might be some sort of issues, corruption, or other errors.
Some Other States Are:
- Online
- Offline
- Copying
- Restoring
- Emergency
- Recovering
- Recovery Pending
- OFFLINE_SECONDARY
Reasons for SQL Server Suspect Database Repair
There can be close to infinite reasons why users want to repair the database’s suspect pages. However, as we can’t list them all here, we are only mentioning the most common ones. These are enough to provide users with an idea of why it is important to execute such a task.
- Corrupted MDF/NDF Data Files in SQL Server.
- Failure in SQL Operations or False Commands.
- Sudden Power Outages & Improper Shutdown.
- Inaccessible Data Files Or SQL Server Crash.
- Hardware Components Failure in the Server.
- Insufficient Space in Disk to Store Data Files.
- Users’ Preference as per Server’s Performance.
- Other Miscellaneous Reasons Depending on Situations.
Automated Tool to Fix Suspect Pages in SQL Server Database
Before we start learning the complex manual method, users must know that there are easy & reliable options as well. In fact, experts & MVPs also recommend users opt for this SQL Server Database Recovery Tool to remove corruption issues. In addition, it offers plenty of features to users as well that can even customize the end results.
Download this tool in your system & then simply follow these five simple steps:
Step-1. Launch the Tool & then Click on the Open button.
Step-2. Select the Quick or Advanced Scan mode here.
Step-3. Now, Preview Database Files & all data objects.
Step-4. Set Destination SQL Server to export the data.
Step-5. Click Export button to fix suspect pages in SQL.
Now, after completing these five steps, all the corruption will be resolved easily. Then we can observe that our database can be brought back to the ONLINE State. Let’s move towards the manual solution if users are still seeking that procedure.
Also Read: Export Table Data to CSV in SQL Server DB
How to Repair Suspect Pages in SQL Server? The Manual Solution
The manual procedure is basically the one using T-SQL commands. Because of using the T-SQL commands, it becomes a bit tough for users. These manual commands are not easy to use but indeed might work if users execute them without a single mistake. Therefore, experienced users try this solution more often than new users.
If you are preparing for the manual method, then be ready for the drawbacks as well. These include:
- Time-Consuming
- Critical Data Loss
- No Customization
- Complex Procedure
- No Advanced Features
- Need Expert Assistance
Step-1. Set Your Database in EMERGENCY Mode
how to repair suspect pages in SQL Server? Well, we are aware of the automated solution but it’s time for the most uncertain manual one. Before we begin, it’s important that in the very first step, we inform our team that the database is undergoing a maintenance check.
After that, we need to put the database into EMERGENCY mode. This will leave users with only the read-only permissions to the database files. This way, the system can troubleshoot the issues of the server that caused this Suspect state.
The command to change the database status into EMERGENCY mode is:
ALTER DATABASE Database_Name SET EMERGENCY
Now, once the database is in Emergency mode, simply move to the next step.
Step-2. Run Database Integrity Check
Now, users need to check the health of the database. The DBCC CHECKDB is the command for that. Here, the DBCC stands for Database Console Command & CHCEKDB is the order to check the database.
The command for SQL Server suspect database repair & database health check is:
DBCC CHECKDB (Database_Name)
This command also recommends suitable repair options from all three available. Either Repair_Allow_DataLoss, Repair_Fast, or Repair_Rebuild option. The system checks the scenario & suggests anyone to the user.
Step-3. Put Database in Single_User Mode
To learn how to repair suspect pages in SQL Server, users just need to put their database into Single_User_Mode. The command to do this is below:
ALTER DATABASE Database_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
After putting the database in single-user mode, finally, we are going to execute the repair command. There is one thing users should be confused about is that we are here only mentioning what to do. In the real scenario, there are plenty of things that users need to know about what not to do.
Step-4. Execute SQL Server Suspect Database Repair Task
To repair the database, its corruption & the suspect pages, execute any one of the three commands mentioned below:
DBCC CHECKDB (Database_Name, REPAIR_ALLOW_DATA_LOSS)
This command might end up in data loss because rather than fixing the corruption, it simply deletes the highly corrupted data files.
DBCC CHECKDB (Database_Name, REPAIR_REBUILD)
Now, comes the rebuild command. This command starts rebuilding the entire database from scratch & this is why it can be really time-consuming. This results in the reduced efficiency of users which will also affect productivity.
DBCC CHECKDB (Database_Name, REPAIR_FAST)
Lastly, we can say if users are short of time, they can run this repair fast command. It executes the entire operation with ease in the minimum possible time. However, users must note that this will only work on minor issues of SQL Database.
Also Read: Read MDF File without SQL Server Database
Step-5. Put Database Back in Muti_User & Online Mode
Now, we know how to repair suspect pages in SQL Server? In fact, we just did. Therefore, it’s time we get things back to normal. Thus, we need to set the database in Multi_USer mode & then Online mode.
Command to Set Database for Multi_User:
ALTER DATABASE Dataabse_Name SET MULTI_USER
Command to Set Database for back to Online mode:
ALTER DATABASE Dataabse_Name SET ONLINE
The Final Say
Finally, we are at the end of this article & totally aware of How to Repair Suspect Pages in SQL Server? We covered both solutions. Obviously, the automated one was much easier & reliable. Also, the reason helped us to go through the in-depth findings.
Now, make sure that there is always expert assistance available for users in case something goes wrong. In a nutshell, users can rely on the automated solution without a doubt. However, the manual solution does not give any guarantee of expected results for the SQL Server suspect database repair task.
