13

Every time I reboot Windows, for some databases I get this error:

The operating system returned error 21(The device is not ready.)

  1. I checked the disk with chkdsk /r - no bad sectors.
  2. I executed DBCC CHECKDB with no errors:

    *(CHECKDB found 0 allocation errors and 0 consistency errors in database)* 
    
  3. If I restart SQL Server the errors disappear.

Windows 10 and SQL Server 2016 Express.

16

Every time I reboot windows, for some databases comes out this error. (OS Error 21 - Device not ready)

This is due to a disk being offline or not being online either at the time SQL Server started, or had transitioned states after SQL Server has been online.

3.If I restart SQL Server the errors disappears

Yes, because the databases have been remounted inside of SQL Server. You could also offline->online the database as well and it would work, assuming the disk device has been fixed.

This can easily be reproduced in a test environment by putting a database on a disk, disabling the disk, running a select query (to get the error), bringing the disk back online and noticing that the select still fails with the same error. The database will need to be remounted in order to work again and not get OS Error 21.

What should you do?

Have someone do some windows tracing to figure out why it isn't coming online initially or why it is going offline (any state transition) or why it is showing ready to windows but really isn't (maybe other drivers need to be loaded for it).

Additionally check any disk filter drivers are up to date for things like anti-virus, host intrusion protections, etc., as those may also be blocking the service/startup/state.

| improve this answer | |
  • I had a similar problem and added a script to restart SQLServer/SqlLaunchPad services after 5 minutes, but that does not work . When I manually restart later then it works fine without issues. The same configuration in SQL Server2014 works without issues – Rajesh Jun 19 '18 at 16:36
  • Change the start mode from Automatic to Delay. This will make sure that the SQLService comes on last (after the disks mount and do their thing). – Jonathan Fite Jul 9 '19 at 16:25
7

I think I've found the cause.

Most likely the problem is due to "Fast startup" power options.

Fast Startup

It'a a Windows technique to reduce the boot time; Fast Startup combines elements of a cold shutdown and the hibernate feature.

Here you can find another article about pros and cons

I have disabled it and the problem seems to be resolved.

| improve this answer | |
  • Great. This is one way of looking at it. The real cause is that some SQL services have not started by the time you see this SQL error. They have not started because of how they are set to "startup" especially if you are indeed using "Fast Startup" for the OS. – Chagbert Jul 24 '17 at 7:15
3

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.
| improve this answer | |
1

The full error I got when connecting to my local default MS SQL instance (2017) via MSSMS is:

The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x000000000ae000 in file 'D:\MSSQL\DATA\tempdev.mdf'. Additional messages in the SQL Server error log and operating system error log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. (Microsoft SQL Server, Error: 823) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=823&LinkId=20476

I started getting this once I moved my tempdb to my new D drive. Doing a start/stop of the SQL service removes the error. Never got this error when everything was on C. Both my drives are SSD and encrypted with Bitlocker, not sure if that could be the issue, maybe the C drive is unlocked very early because the operating system needs it, and D drive gets unlocked later.

  1. As per Max's answer (https://dba.stackexchange.com/a/175115), disabling "Fast Startup" fixed my problem. As per the article Max links to (https://www.howtogeek.com/243901/the-pros-and-cons-of-windows-10s-fast-startup-mode/) it's quite obscure to find, in the "Choose what the power buttons do" and then "Change settings that are currently unavailable".
  2. Unlike Venvig's answer (https://dba.stackexchange.com/a/226115), setting the "SQL Server" service to Startup Type = "Automatic (Delayed Start)" did also fix my problem (with Windows'Fast Startup re-enabled).
| improve this answer | |
0

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

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).

| improve this answer | |
0

This problem has irritated me as well. I have 5 dbs hooked onto my SQL Server instance, 3 of which are working fine, but 2 of which complains

The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000204000 in file 'E:\xxxxxxxx.mdf'

Here is my solution.

  1. Turn on Services.msc, locate the service called SQL Server (instance name), right click and restart it.
  2. Go back to ssms, refresh your db, and things should work.

On a side note, I did try taking db offline/online method. It did not work in my case. Brute force restarting the sqlserver service worked nicely. this might be a problem for those whose stake of taking all dbs offline are too high. However, if you're just doing local development like me, then this solution should be fine.

| improve this answer | |

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.