Question: I have one master and one detail table as following

```
Master:-
   BatchID
   BatchStatus
Detail
   DetailID
   BatchID
   DetailRecordStatus (Possible values 1,2,3)

```

I am trying to write a single a query that can update the Batch Status in Master based on `detailRecordStatus` values equal to 2 or 3

Answer: 
These are my observations and how I solved the problem (for the benefit of others who may have the same problem)

* I was using amazon ec2 instance running Sql server.
* I had an EBS Block device attached to the ec2 instance, that mapped to the D: drive.
* My data and logs were in the D: drive.
* When I stop the ec2 instance and bring it up later, I always experienced the error of "device not ready" and the databases would not come up.
* I tried setting the MSSQLSERVER service with "Delayed startup".
* However, from the sql server logs I found that the delay was not honored and MSSQLSERVER started up right along with the boot.
* From the eventviewer I observed the time at which the D: drive becomes healthy.
* From the sql server logs, I noted the time at which SQL Server is starting up my user database.
* I observed that, D: drive is only available after 6 seconds later; and obviously the "Device not ready" error shows up.
* I also noted that that the "Delayed start" was not honored because there was another service named "SQL SERVER LaunchPad" which startups up "MSSQLSERVER".
* I don't need the Analytics capability of the "Launchpad". So I disabled that service.
* **Now "MSSQLSERVER" starts up with a delay and can find the D: drive files.**
I have come across the same problem many times and thought I should share my solution (notwithstanding the already provided answers):

So I have two SQL instances (SQL 2008 and SQL 2017). The error does Not manifest in my SQL08 Instance but on SQl17. This is caused by the "account Credentials" provided during Installation/setup of each SQL instance:

[![enter image description here](https://i.stack.imgur.com/ld3f7.jpg)](https://i.stack.imgur.com/ld3f7.jpg)

This can be seen under Windows Services. The SQL08 was set to use the "Local System Account" whilst the failing SQL17 was set to "NETWORK ACCOUNT" during setup.
So just change that and restart the SQL service here (or restart instance in SQL browser).

The second part to this problem is unique to SQL Server 2017 CTP 2.0 when using SQL Server Management Studio V17 in which case the SMO switched to using "*sys.dm\_os\_enumerate\_fixed\_drives*" instead of the old "*xp\_fixeddrives*" to get free space information of your local disk.
To workaround this, go to the DEVICE MANAGER and temporarily disable the cited drive (in my case was drive "G" which is just my DVD-ROM drive).