A vexing issue

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_helpdb 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.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_helpdb 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.
>
>|||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.com> 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_helpdb 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.
>>
>|||> 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.com> 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_helpdb 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.
>>
>>
>sql