You might be asking why on earth would you want to get a database into an undesirable state, more specifically into a Recovery Pending state. Well, in my case, I had a client database that had entered into this state due to a failure of storage. Thankfully, this database was not being used for production, so I had some time to determine the best way to fix the issue.
A phrase that was often used during my time in the fire service, was “Try Before You Pry”. Does the front door of the house need to be kicked in? It may be unlocked and by trying before prying (with my boot) I can prevent damage to the door. In these types of scenarios, this philosophy holds true. Try things out on non-critical databases will help prevent any further damage.
In this instance, I want to try it before forcing something that might be damaging. This meant I had to get a test database into a recovering state. Once it is in the state I need, then I can attempt different methods to recover the database properly. Once a successful solution is determined, I can then confidently pry against the damaged database in production knowing that I am using a validated solution.
DISCLAIMER: DO NOT DO THIS ON A PRODUCTION SQL SERVER INSTANCE.
You have been warned.
DISCLAIMER: DO NOT DO THIS ON A PRODUCTION SQL SERVER INSTANCE.
You have been warned. Again.
How do you get a database into a recovery pending state?
Here is how I did this:
- Start a new transaction.
- Create a new table.
- Stop the SQL Server service
- Rename/Delete the database log file.
- Restart the SQL Server Service
The database will be in a recovery pending state upon the restart of the SQL Server service.
Why is the database in recovery pending?
When the database attempts to come back online, it will be put into a recovery pending state because the log file is not present but there was an open transaction when the service was shut down. In normal operations, even with an open transaction, SQL Server would go through the recovery phase of the transaction log. In the rollback phase of recovery, SQL Server would attempt to rollback and transaction that was open at the point of restart and undo the changes. Since the log file no longer exists, it is unable to do so.
Therefore, the database is now in the recovery pending status. It’s pending recovery because there was an open transaction, but SQL Server is unable to bring the database into a consistent state.
When this occurs, you will see something like this in the error log:
If the database is shut down cleanly and the transaction log file remove/renamed/etc., SQL Server will just rebuild the log file for you.
Summary
Sometimes it is useful to be able to put a database into a specific state of being so that you can validate solutions before attempting to perform an action in a Production environment. Just remember to try it before prying it. Not doing so could just make things worse so being overly cautious is not a bad thing.
© 2021, John Morehouse. All rights reserved.
The post How to put a SQL Server Database into Recovery Pending first appeared on John Morehouse.