How to put a SQL Server Database into Recovery Pending

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.


You have been warned.


You have been warned. Again.

How do you get a database into a recovery pending state?

Here is how I did this:

  1. Start a new transaction.
  2. Create a new table.
  3. Stop the SQL Server service
  4. Rename/Delete the database log file.
  5. 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.


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.

2 Replies to “How to put a SQL Server Database into Recovery Pending”

  1. But how would you recover this database, assuming the log file was on another drive that failed and was unrecoverable? Is the only option to restore from backup?

  2. Hi Kenneth – Thanks for stopping!

    Restore is an option, but you can fix it by setting the database into Emergency mode and running CHECKDB with repair_allow_data_loss. If I recall this would rebuild the log file and any transaction in flight would be lost but the database would come back online in a consistent state. If data loss isn’t an option, a restore to a point in time is the better option here. I should write a follow up post on how I was able to fix it. =) Thanks for the idea!

Hey you! Leave me a comment and start a discussion!

This site uses Akismet to reduce spam. Learn how your comment data is processed.