We help IT Professionals succeed at work.

SQL Server FileTable "Recovery pending status"

Hi Experts,

We have an EDM in our c# app, and we use FileStream/FileTable technology to store documents (Word, PDF, etc.).  We have a database with only 2 filetable tables to make the job. (other data are stored in a second classical db).

One of our db in production was in "Recovery pending status" today, we see a lot of reasons and workaround to fix it.

For this time, we need to detach an reattach it, but it is not satisfactory for us, we want to know why and avoid this issue.

For information and for different reasons, our application needs to open an Office doc (word / excel) from c# and we use the Filetable share directory like \\serveur\instance\FS for that.
A strong constraint is that the user can open the document and save it transparently.

Our questions are:
1. At the moment where the db is in "Recovery pending status", are there any tools or SQL statement to help us to understand the reason?
2. Is there a daily check to be done regularly on the databases to prevent these issues?
3. Of course, this db contains about 50GB to 300GB and important documents, is there a risk of losing the data?
4. What are the usual tools to repair, for example: if we detach the db and we cannot reattach it, what to do?

Thanks a lot for your Help.

With kind regards
Herve
Comment
Watch Question

EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
for start:
what is your sql server version/sp/edition?

Can you ask your DBA to check sql server errorlog  where you may find the reason?

you may have issue doe to IO problems

or
permissions
see the similar case
with a work around " the workaround is to use system account or provide admin permissions to the service account."
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b7dcab11-e17c-48f9-a08a-0a15a3f6b12b/database-in-recovery-pending-status?forum=sqlexpress



\
Herve BlondDeveloper

Author

Commented:
Hi EugeneZ, thx for your help.

sql server version/sp/edition? : Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)   Jul 12 2019 17:43:08   Copyright (C) 2017 Microsoft Corporation  Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)

Can you ask your DBA to check sql server errorlog  where you may find the reason? We are a small company, I'm also the DBA :-) Hereafter a screenshot of the errorlog : (in green before the issue, and yellow after)
SQL Server Errolog
We did the DBCC CHECKDB and no error were found, and we applied the Cumulative Updates (may be it was very important to do that because it was the RTM version!!)

And about the "account or provide admin permissions to the service account." I don't think that’s the use case for me, because it happens suddenly,  everything worked fine for a few days before that.

Best,
Herve
EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
do you have a good backup of this DB? you may need it.


Try to reboot (PC\Server)
see if you still have this issue

if you do not have a good db backup .

You'd need to do run DBCC Checkdb for this DB ( "Recovery pending status")



more
How to troubleshoot Msg 5180 in SQL Server
Could not open File Control Bank (FCB) for invalid file ID
https://support.microsoft.com/en-us/help/2015747/how-to-troubleshoot-msg-5180-in-sql-server

and how to run DBCC CHECKDB for such cases

ALTER DATABASE [YourDBName] SET EMERGENCY;
GO
ALTER DATABASE [YourDBName] set single_user
GO
DBCC CHECKDB ([YourDBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE [YourDBName] set multi_user
GO
Developer
Commented:
Hello, thx to EugeneZ for the very interesting information provided. We think that the CU was the solution, After 1 week the problem did not happen again. Best.
EugeneZ ZhitomirskySQL SERVER EXPERT
CERTIFIED EXPERT

Commented:
Hi, it is a good news.
What exactly did bring dB online?
Could you please clarify?
Herve BlondDeveloper

Author

Commented:
Hi EugeneZ, sorry I just saw your last message.... Better late than never : Db was not corrupt and just the detach / attach done the job for me.  And after the CU16 update has been applied, everything works normally.

Best,
Hervé