Tip: RECOVERY_PENDING is not the same as RECOVERING

I’ve seen this mistake more than once, from DBAs with a middling to fair amount of experience. So it bears repeating. RECOVERY_PENDING is very different from RECOVERING.

Definitions

RECOVERING means that things are happening, and all may be well. “Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable.”

RECOVERY_PENDING means that something is wrong, and YOU have to fix it. “SQL Server has encountered a resource-related error during recovery. The database is not damaged, but files may be missing or system resource limitations may be preventing it from starting. The database is unavailable. Additional action by the user is required to resolve the error and let the recovery process be completed.”

(Definitions from the Technet article “Database States“.)

Finding the database state

Finding the state of your databases is easy. You can look in Object Explorer, and it’ll say so right there:
recovery_pending
recovery_pending2

You can look in sys.databases, which is the preferred method:

SELECT name, state_desc
FROM sys.databases;
— Optional: WHERE state_desc <> ‘ONLINE’;

(Why is that preferred? Because it’s programmatic, and so repeatable and fast and usable in automated scenarios. And ot’s more accurate, as the GUI can glitch or cache information, whereas sys.databases never never does. )

And of course, you can look in the SQL Server error log and see if any errors popped up while recovering databases (for example, during SQL Server startup, or when the database was set online).

When good databases go RECOVERY_PENDING

recovery_pending3

One of the more common scenarios for RECOVERY_PENDING is that a database file is missing, or renamed, or moved, or who knows what. That’s definitely the place to start. After all, the error log – remember the error log? – will often tell you that this it the problem. Look here:

FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Demo.mdf for file number 1.  OS error: 2(The system cannot find the file specified.).

In this case, it’s very clear; it can’t find the file. As it turns out, that’s because someone renamed the file while the database was offline (either while SQL was stopped, or the DB was in an OFFLINE state). So I renamed the file properly, set the database to ONLINE, and all was well.

You’ll occasionally get RECOVERY_PENDING after a service restart, for no good reason, and I don’t know why. SQL Server gives you error 17204, “Open failed: Could not open file …. OS error: 32(The process cannot access the file because it is being used by another process.)” And then error 5120, which carries the same message.  This is nearly always resolvable by setting that particular database offline and online again.

Stop. Rebooting. The server. Durnit.

Note that I do not recommend rebooting the server, or even restarting SQL. It’s a problem with a single database. I’d very much like this message to get out there: Most problems in SQL are solvable without a restart!  Especially THIS problem.

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

4 thoughts on “Tip: RECOVERY_PENDING is not the same as RECOVERING

  1. Ian Yates

    Good article pointing out the distinction 🙂 I had this issue at a client’s site where they’d set AUTO_CLOSE ON (yuk) and had anti-virus on the server scanning files on open with no exclusions (also yuk). Occasionally it took a little too long to open the file due to the AV and we’d get this sort of issue.

  2. Dave

    By the far the most useful article on this particular error that I found in the last hour of searching. In my case, an unscheduled server reboot appears to have tricked SQL Sever into thinking the data and transaction log files were missing even though they were not. Taking the database offline and back online fixed it. Thank you for the help.

Comments are closed.