> It certainly seems strange that Microsoft did not provide an escape hatch for this "Recovery
> Pending", i.e., in limbo, scenario.

What would the escape hatch be?

It is important to differentiate between a "real" Recovery Pending (RP) situation and a "false" one.

It seems you had a false RP situation. By "false" I mean that SQL server did already complete
recovery work, which is why you could detach and attach the databases without SQL Server
complaining. If you had a "real" RP situation, then SQL Server would *not* allow you to attach a
database for which recovery cannot complete. Why you experienced this false RP, I don't know.
Sometimes I see users who don't refresh the GUI, quite simply. There could be other things as well,
of course, I can't say. One option would have been to open a case with MS and have them sort out
whether it was a real or false RP case and if it was a false one sort out the bug in the product
that lead to this false RP situation.

There is a reason why there isn't an escape hatch for a *real* RP situation (almost, see end of this
paragraph) . It would leave the database in an inconsistent state. The data is inconsistent from a
logical viewpoint (foreign key and other constraints cannot be trusted, half-completed transactions
etc). But also from a physical viewpoint (system tables modifications half-done). Basically you
would have a useless database. This last two weeks I have worked with such a database. I've spent
two weeks to get inconsistent data out of 3 tables (yep, only 3 tables over two weeks). And I petty
the soul who will try to re-integrate this crap into the production database (which was restored
from a 1 month old backup). As you can imagine, this isn't something that MS want to expose to
normal users out there. If you do feel adventurous, you can read up on "Emergency mode". This is
your escape hatch. This should not be used unless you are en expert in SQL server, and you prefer
inconsistent data over your most recent backup.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Earl" <earl@nospam.com> wrote in message news:OYbl276jIHA.4940@TK2MSFTNGP02.phx.gbl...
> Thanks for the ideas Tibor. Nothing I could do would get those DBs out of "recovery pending". But
> tonight, on the off-chance that it might work, I went ahead and tried to detach. It gave me an
> error message that it could not close the files, but in fact, once I refreshed, it HAD detached
> the files! Just to confirm that they were actually detached, I created a new directory and moved
> the .mdf and .ldf files in there with no problem. I then re-attached and the previously
> in-recovery databases are functioning normally.
>
> It certainly seems strange that Microsoft did not provide an escape hatch for this "Recovery
> Pending", i.e., in limbo, scenario.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in message
> news:6B5D9B92-E6AC-485B-9053-E9BF2B109A94@microsoft.com...
>> Recovery Pending means that SQL Server need to do recovery for the database to bring it to a
>> consistent state. Recovery consists of REDO and UNDO, both parts are based on the entries in the
>> transaction log (ldf file). SQL Server feel that the ldf files are fishy or something similar,
>> and hence it cannot do recovery.
>>
>> In most cases when I see something similar, it was somebody who deleted the log file or used some
>> other tool to reduce file size. If you are 100% certain that something like this didn't happen,
>> then you should carefully investigate the SQL Server errorlog file for all related error
>> messages. Also investigate the Windows eventlog for clues to what has happened to these files.
>> Int he end, if you cannot get these files back to healthy state, you are in for RESTORE DATABASE.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "Earl" <earl@nospam.com> wrote in message news:uiesseQjIHA.5160@TK2MSFTNGP05.phx.gbl...
>>> Strange problem that I just noticed. I use a handful of databases in SQL2005 Express for
>>> development. All of these databases except the one I've been using most recently "cannot be
>>> opened due to inaccessible files or insufficient memory'.
>>>
>>> All of the databases show "recovery_pending" for status.
>>>
>>> I tried to set them online with alter database, but get the message:
>>> "File activation failure. The physical file name "...\mydb_log.ldf" may be incorrect."
>>>
>>> Looking at all of the "recovery_pending" databases, I see that all of the log files are showing
>>> as "may be incorrect."
>>>
>>> sp_db gives me the message: "No permission to access database <mydb>"
>>>
>>> DBCC CheckDB gives me the same message.
>>>
>>> Thinking back through what has occured in the last few months (since these were last used), I've
>>> done some minor system maintenance such as defrag and Windows Updates, but nothing else has been
>>> changed. And the files still reside in the directory where the log file is pointed to.
>>>
>>> I'm baffled as to how to get these out of recovery, so any advice is appreciated.
>>>
>>>
>>>
>>
>
>