A vexing issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Earl - 23 Mar 2008 18:02 GMT

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.

Reply to this Message

Tibor Karaszi - 23 Mar 2008 20:03 GMT

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.

Signature

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

> 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
[quoted text clipped - 17 lines]
>
> I'm baffled as to how to get these out of recovery, so any advice is appreciated.

Reply to this Message

Earl - 27 Mar 2008 03:05 GMT

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.

> 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
[quoted text clipped - 35 lines]
>> I'm baffled as to how to get these out of recovery, so any advice is
>> appreciated.

Reply to this Message

Tibor Karaszi - 27 Mar 2008 09:12 GMT

> 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.

Signature

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

> 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
[quoted text clipped - 38 lines]
>>>
>>> I'm baffled as to how to get these out of recovery, so any advice is appreciated.

Reply to this Message