0

We just migrated our database server from Windows 2012 to Windows 2016, with SQL Server 2014. We have approximately 200 databases running on this server, most of them around 5mb in size.

Almost every morning, I'm getting a report from users saying the databases are inaccessible. When I log in using SQL Management Server, 2-3 of the databases are marked as "Recovery Pending".

If I take them offline, then online again, the database becomes available, with no apparent loss of data.

I've looked through the Windows Event Logs, and I can't see a single error regarding this.

Is there an SQL Server log file somewhere that might shed some light on why this is happening? It seems to happen over night when the office is closed and no-one is using the databases.

0

Gavin, run [sp_readerrorlog 0,1] on the Sql Server instance and review the Sql Server error log. There will hopefully be something in it which points to why the databases are being placed in "Recovery Pending".

5
  • The error I seem to be getting is: FCB::Open failed: Could not open file E:\Elecworksdata\MSSQL12.TEW_SQLEXPRESS\MSSQL\DATA\tew_catalog_log.ldf for file number 2. OS error: 32(The process cannot access the file because it is being used by another process.). What else other than SQL Server could be using this file? SQL Seems to restart the databases every 20 minutes, normally they restart fine, it only seems to be in the evening they fail to start. (9pm and midnight, recently). There's a backup that runs at 1800 (Crashplan), but it should be finished in less than an hour. – Gavin Coates Apr 6 '17 at 8:06
  • "SQL Seems to restart the databases every 20 minutes" - Is the "Auto Close" properties of the databases which are having the issue set to True or False? AutoClose can cause all types of issues. If it's not that, it could be virus scanning. If not that, ensure that the sql server account running the Sql Server Service has access to the folder and files for the databases. – rvsc48 Apr 6 '17 at 12:11
  • 1
    I disabled the virus checker a while ago, in case it was that. AutoClose is indeed set to true - I've disabled it now, hopefully that will solve it. If it does, is there a way to set this to default to off for all newly created databases? – Gavin Coates Apr 10 '17 at 7:32
  • Gavin, yes, if you change the Model database's properties of AutoClose to FALSE, any newly created databases should follow that pattern. – rvsc48 Apr 10 '17 at 13:31
  • Having AutoClose set to false is definitely the thing to do however I would recommend to watch out for reboots (ie. after Windows Updates) as I expect you'll see the same behaviour – Joel Mansford Apr 19 '17 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.