Hadzicki
asked on
Database reads Recovery Pending after reboot
We have had an issue that started a few months back where after a reboot, we will have random databases go (Recovery Pending) .
- Servers are both 2016 and 2012R2 running SQL server 2014 SP3CU3 / SP3CU2
- We are running CB Defense and have exemptions listed for any .ldf and .mdf file types
- Event viewer shows no errors in system or application
I am able to fix the issue by running a tsql script but when the server gets rebooted it is a 50/50 that the issue happens again.
After looking on line I found a post about looking into logs under mssql
- When digging into the mssql\logs I have the following entries
2019-04-23 19:22:13.78 spid17s Error: 17204, Severity: 16, State: 1.
2019-04-23 19:22:13.78 spid17s FCB::Open failed: Could not open file D:\SQL\DB\XXXX.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
2019-04-23 19:22:13.95 spid17s Error: 5120, Severity: 16, State: 101.
2019-04-23 19:22:13.95 spid17s Unable to open the physical file "D:\SQL\DB\XXXXX.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
2019-04-30 08:16:05.86 spid18s FCB::Open failed: Could not open file D:\SQL\DB\XXXXX.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
2019-04-30 08:16:05.88 spid18s Error: 5120, Severity: 16, State: 101.
2019-04-30 08:16:05.88 spid18s Unable to open the physical file "D:\SQL\DB\XXXX.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
- Servers are both 2016 and 2012R2 running SQL server 2014 SP3CU3 / SP3CU2
- We are running CB Defense and have exemptions listed for any .ldf and .mdf file types
- Event viewer shows no errors in system or application
I am able to fix the issue by running a tsql script but when the server gets rebooted it is a 50/50 that the issue happens again.
After looking on line I found a post about looking into logs under mssql
- When digging into the mssql\logs I have the following entries
2019-04-23 19:22:13.78 spid17s Error: 17204, Severity: 16, State: 1.
2019-04-23 19:22:13.78 spid17s FCB::Open failed: Could not open file D:\SQL\DB\XXXX.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
2019-04-23 19:22:13.95 spid17s Error: 5120, Severity: 16, State: 101.
2019-04-23 19:22:13.95 spid17s Unable to open the physical file "D:\SQL\DB\XXXXX.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
2019-04-30 08:16:05.86 spid18s FCB::Open failed: Could not open file D:\SQL\DB\XXXXX.mdf for file number 1. OS error: 32(The process cannot access the file because it is being used by another process.).
2019-04-30 08:16:05.88 spid18s Error: 5120, Severity: 16, State: 101.
2019-04-30 08:16:05.88 spid18s Unable to open the physical file "D:\SQL\DB\XXXX.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
ASKER
Process explorer is only showing the sqlsvr.exe hitting against any .mlb files. Problem is, as soon as the computer boots and we open sql we see it in the recovery pending state so it is hard to catch anything that could be calling it.
There is only the single sql edition install on these servers (happening to multiple servers)
These are VMs (VMware, exi 6.5)
There is only the single sql edition install on these servers (happening to multiple servers)
These are VMs (VMware, exi 6.5)
Hello Hadzicki,
if you don't automatically start the SQL Server service, set it to manual in servcice settings, and then reboot can you catch any other access to those files?
leflon
if you don't automatically start the SQL Server service, set it to manual in servcice settings, and then reboot can you catch any other access to those files?
leflon
I assume
Also, as SQL runs on VM's - did anyone checked the VM's for any messages and any storage latency/issues? How are the drives where SQL database files are stored attached to the VM? Local disc or LUN's?
.mlb fileswas a typo and you refer to MDF files right?
Also, as SQL runs on VM's - did anyone checked the VM's for any messages and any storage latency/issues? How are the drives where SQL database files are stored attached to the VM? Local disc or LUN's?
ASKER
Just wanted to give an update. It appears the issue is caused by our application control software. Thanks to everyone for their help
ASKER
Just to note for anyone else who has this issue, there was no logs being reported of a problem, it was only by luck while searching that we found 1 post online on their support forms and opened a ticket to confirm.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Also are they running on VM's by any chance?
Alternatively you should be able to use https://docs.microsoft.com/en-us/sysinternals/downloads/process-explorer and using the "Find" feature against youd database name(s) that are in question you should see the proces(es) that have the files opened already.