Sometimes even when you have done everything perfectly, you have rebooted the SQL servers and restarted the whole system you may face a problem of SQL servers still in recovery mode. SQL is such a complex database system that if you do every task in hurry you will create more problems for yourself. But you need not worry about this as some common issues keep occurring for a long time in SQL servers. These errors are definitely removable. First of all, let’s look at the crux and function of SQL Server in Recovery Mode After Reboot.
Whenever you try to restart of SQL Server, all databases would undergo in “Recovery” process. This is the phase where the database needs to come back online in a consistent(Normal) state.
But because your database was not shut down rightly on the restart, the database must go through crash recovery. When the database is not shut down rightly, SQL Server must make sure that transactions written to the transaction log have been restored properly against the data files.
All transactions need to be written to the transaction log. But updating data will be initially done in memory. Updates to the physical data files are done by the checkpoint. The nonsimultaneous nature of the data file updates is why a crash or unclean shutdown requires extra work on startup and recovery.
Causes of SQL Server Database in Recovery Mode after Reboot
- SQL Server starts up after a shutdown
- When the database was not shut down correctly.
- After a cluster or phase-failover.
- Due to a database mirroring fail.
- Due to the size of the transaction log file
- SQL restarted during a long-running transaction.
- Due to a bug in SQL Server which is fixed with the help of some patches
- When restoring a database from backups.
- When bringing a secondary database online and offline.
So There is three sub-phases with-in this process that needs to be taken care of. These are Analysis, Roll forward, and Rollback. Let’s take a look at each of these to learn in detail about why these are an important part of the process
Stages in the Recovery process
- Analysis
This stage Starts at the last checkpoint in the transaction log. This stage generally looks for pages that might be dirty at the time SQL Server stopped. There may be cases where an active transaction table is also built for the uncommitted transactions at the time when the SQL Server may have stopped.
This is the phase where SQL Server will check the LOG file to know how much work is needed in the next two phases. So basically it will find the starting point from which rolling forward can be done during the redo phase in a hassle-free way.
- Roll Forward
This is the stage where every change is visible in the log files. Now completed transactions from the transaction log need to be reviewed to ensure the data file has been completely updated. If changes are not done correctly they can get lost
This phase will take those transactions that were committed after the most recent checkpoint and redo them, to ensure they are consistent in the data file.
Important note: In SQL Server Enterprise edition, fast recovery will allow the database to come online and will be available after this phase of recovery. If you are not using Enterprise Edition, the database will not be available until after the Undo phase has been completed.
- Rollback(undo phase)
In this phase wherein, the rolling back of the active transactions occurs. This means if there were any uncommitted transactions during the recovery of the database or in the above phases, they need to be rolled back in order to bring the database to a consistent state. In other words, any transaction that was not committed at the time SQL Server stopped is undone.
How to solve this problem?
Solution 1
The first step is you need to check for the error logs. You can get to the error logs via the SSMS GUI or command search, or by manually opening the error log files stored on the server-Connect to SQL Server using SQL Server Management Studio by providing the correct name.
To find the where the error log files are you can run the command: “sp_readerrorlog”
Next, go for Starting up database ‘TestMe’. Test me helps in opening the specific files and starting the recovery. After some time, you should see phase 1-” Recovery of database ‘TestMe’ is 0% complete”. It will automatically scan and recover by going through every phase as discussed above. Important note: You don’t need to do anything as all these messages would be informational and no action is required on them,
Solution 2
Break the database mirroring points
To solve this problem, you can also remove database mirroring and the database will return to normal. The steps to remove database mirroring using SQL Server Management Studio are:-
- Step 1:
During a database mirroring session, connect to the server then go in Object Explorer, click the server name to expand the server tree.
- Step 2:
Expand “Databases”, and select the database.
- Step 3:
Right-click on the database, click on Tasks and then click Mirror. This will open the Mirroring page of the Database Properties dialog box.
- Step 4:
“Select a Page”, click on “Mirroring”.
- Step 5:
To remove mirroring, click “Remove Mirroring” and Click “Yes”, the session will be stopped, and mirroring will be removed from the database.
Solution 3
Doing the backup and restoration after working on the above solution trough a professional SQL Database Recovery tool. It is very important to recover MDF and NDF files so that there is no data loss. So as this problem takes much time you can combine 2 solutions to speed up the recovery.
Salient features are:-
- Dual recovery mode(standard and advance) to deal with different levels of corrupt log files.
- It supports SQL servers Row compression and Page compression
- A user-friendly tool with a smart preview to see the recovered data
- Supports all SQL editions and windows editions.
As we can infer from the process, It can be very time-consuming, and to Fix SQL Server Database in Recovery Mode After Reboot there could be a need for combining 1 or 2 solutions. So it is very important to be patient. Don’t reboot the system in the hurry. Always shutdown the SQL in a proper manner and making sure any database is not running.