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.
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:
You can look in sys.databases, which is the preferred method:
SELECT name, state_desc
– 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
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.