2

EDIT: Restored to a restore point from three days ago, prior to a "Critical Update", and the databases are back.

This is, I believe, the culprit update: September 10, 2019-KB4514601 Cumulative Update for .NET Framework 3.5, 4.7.2 and 4.8 for Windows 10 Version 1809 and Windows Server 2019

Last night at 10:01 PM, per the System Log, the SQL Server 2017 service on my development PC terminated:

The MSSQL$SQL2017 service terminated with the following service-specific error: Only one usage of each socket address (protocol/network address/port) is normally permitted.

and this other error was also there 15 seconds later:

Installation Failure: Windows failed to install the following update with error 0x80070643: Feature update to Windows 10, version 1903.

So it appears to have had something to do with the #(@*@$#&U Windows nightly update mechanism. There is only one of my databases remaining (in addition to the system databases). How do I get them back??

EDIT: When I attempt to reattach the files I am getting an error:

Unable to open the physical file [H:\foo\my.mdf] Operating system error 5: (Access is Denied.). Microsoft SQL Server, Error: 5120

And when I go to the folder in question where the MDF and LDF files are stored, and attempt to give NT Service\MSSQL$SQL2017 read/write access to the folder, I get this error:

An error occurred while applying security information to: [path to the LDF file of a database that accesses the CLR] Failed to enumerate objects in the container. Access is denied.

and then it goes on to give the same error for the other LDF files in the folder as well as certain MDFs, not all of them.

Upon rebooting, I get this error again:

The SQL Server (SQL2017) service terminated with the following service-specific error: Only one usage of each socket address (protocol/network address/port) is > normally permitted.

Tim
  • 359
  • 1
  • 9
  • 3
    Check there folder where your mdf files are, are the mdf files still there? – Yannick Liekens Sep 13 '19 at 12:07
  • Yes, the MDF files are there. – Tim Sep 13 '19 at 12:10
  • Log files as well? If so, you can just attach them again – Yannick Liekens Sep 13 '19 at 12:11
  • I was concerned that something had been royally screwed up, so that reattaching might corrupt them. I will makes copies of the files first. Do you know what caused them to disappear? – Tim Sep 13 '19 at 12:14
  • In fact, I am getting an error when I try to reattach. Unable to reattach. `Operating system error. Access is denied.` – Tim Sep 13 '19 at 12:17
  • Make sure your SQLServer service account has both read and write access to the folder where your files are located. Check this : https://stackoverflow.com/questions/18286765/sql-server-operating-system-error-5-5access-is-denied – Yannick Liekens Sep 13 '19 at 12:19
  • 2
    You could check what processes have open file handles on the database files with [process explorer](https://docs.microsoft.com/en-us/sysinternals/downloads/process-explorer). [Here](https://serverfault.com/questions/1966/how-do-you-find-what-process-is-holding-a-file-open-in-windows) is an example of using it. Are you sure that you are logged in with a user that has enough permissions and that there are no other instances running on the machine? – Randi Vertongen Sep 13 '19 at 12:22
  • I have now rebooted the machine but the Access is denied error persists. A reboot would clear any open file handles, right? – Tim Sep 13 '19 at 12:38
  • @Aaron Bertrand: H: is a hard drive in my PC. These files have been in that folder for months, working properly. There is another instance, but of SQL Server 2012, on the machine. That second instance looks to a different folder. – Tim Sep 13 '19 at 12:47
  • @Tim Please check if SQL Server service account has access to the the data files. – Rajesh Ranjan Sep 13 '19 at 13:02
  • @Aaron Bertrand: The other instance is not relevant. It is not attaching to these database files but to files in another folder. I already mentioned that fact. The permissions issues were created by the nightly update. They did not exist before that. The Windows Update screwed this up, not me. – Tim Sep 13 '19 at 16:16
  • @Rajesh Ranjan: It did not have access (although it formerly did have access). But I am unable to grant permissions to the SQL Server service account. I get this error, as mentioned in the question: "Failed to enumerate objects in the container. Access is denied." – Tim Sep 13 '19 at 16:32
  • @Tim you should contact the system administrator if you aren't able to grant permission to SQL Server service account. I faced this type of issue when data files become inaccessible to the SQL server service account. – Rajesh Ranjan Sep 13 '19 at 16:36
  • I am the system administrator in this instance. This is my personal development PC, on which I had a developer license instance of SQL Server 2017 running. – Tim Sep 13 '19 at 16:44
  • @Aaron Bertrand. These databases have been working properly for months, in fact, as late as last night before I went to bed. I woke up this morning to find that Windows had rebooted itself (per Microsoft's invasive update policy) and the databases were inaccessible. I'm pretty sure it's the update to the CLR that is the culprit. You'll probably have the chance to help other people with this same question. – Tim Sep 13 '19 at 16:52
  • Okay. If you're so insistent Microsoft broke you, you should contact Microsoft support. Good luck. – Aaron Bertrand Sep 13 '19 at 17:04

0 Answers0