Database in Recovery pending

Question

Database in Recovery pending

Admingod

SSCertifiable

Points: 5997
More actions
April 29, 2019 at 9:47 pm

#3550201

Has anyone has seen below error message? I have resolved the issue, no recovery pending state or log space issue or tempdb issue, but trying to understand is this has anything to do with sql server? Looks to me related to storage,I/O or Network issue. Any thoughts?
SQLServerLogMgr::LogWriter: Operating system error 2(The system cannot find the file specified.) encountered.
spid60 Error: 9001, Severity: 21, State: 4.
The log for database is not available. Check the event log for related error messages. Resolve any errors and restart the database.
spid4s SQLServerLogMgr::LogWriter: Operating system error 2(The system cannot find the file specified.) encountered.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

oogibah SSC Eights! Points: 827 More actions · Answer 1

Looks to me that either your database log file has somehow been deleted or your database no longer have access to it... which wouldnt make any sense.. If I remember correctly the database will remake the log file if you bounce it though.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

So how did you fix the issue? And when did the issue occur? After a restart or what?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 3

Usually it's storage related but what did you do to resolve the issue?

Sue

Admingod SSCertifiable Points: 5997 More actions · Answer 4

Thanks! I was able to solve this by taking database offline then immediately bringing it back online.

Sue_H SSC Guru Points: 90860 More actions · Answer 5

Did you check the Windows event logs when it happened? Most likely you can find some things in those logs.

Sue

HanShi SSC-Dedicated Points: 33507 More actions · Answer 6

Admingod wrote:

Thanks! I was able to solve this by taking database offline then immediately bringing it back online.

This sounds like the storage (SAN/mountpoint?) where the LOG file was located was not yet completely mounted/online when the SQL service started. If this happens more often after a restart, you could set the startup type of the SQL server service to "delayed start".

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

HanShi wrote:

Admingod wrote:
Thanks! I was able to solve this by taking database offline then immediately bringing it back online.
This sounds like the storage (SAN/mountpoint?) where the LOG file was located was not yet completely mounted/online when the SQL service started. If this happens more often after a restart, you could set the startup type of the SQL server service to "delayed start".

HanShi wrote:

Admingod wrote:
Thanks! I was able to solve this by taking database offline then immediately bringing it back online.
This sounds like the storage (SAN/mountpoint?) where the LOG file was located was not yet completely mounted/online when the SQL service started. If this happens more often after a restart, you could set the startup type of the SQL server service to "delayed start".

My thoughts, exactly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Admingod SSCertifiable Points: 5997 More actions · Answer 8

Admingod

SSCertifiable

Points: 5997

May 1, 2019 at 8:40 pm

#3605001

Thanks to everyone!

Admingod SSCertifiable Points: 5997 More actions · Answer 9

Storage team did not find any issues on their end. Not sure if they are properly looking at their end. If not, do you think it could be a network issue? SQL Server was never restarted when this issue happened, however, the window log shows connection to the target was lost.

oogibah SSC Eights! Points: 827 More actions · Answer 10

I really doubt it would be a network issue unless your log file is for some reason stored on another server?

Sue_H SSC Guru Points: 90860 More actions · Answer 11

No, not the network. The connection was briefly lost for some reason so you would look at everything from the server to the SAN. And that's not just the SAN and the server - there are components in between. HBA, switch, fabric, cables, etc. Take a look at this article - the beginning pages have some images of how that looks which should give you an idea.

SAN Conceptual and Design Basics

Sue

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 12

Do you have any security software doing on access scanning? If SQL db files are not excluded I could see this type of issue popping up, especially after a system restart.

Joie Andrew
"Since 1982"