4

The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x0000000001c000 in file 'E:\SQL Database\S*****d\NewAdvWorks.mdf'. Additional messages in the SQL Server error log and system event 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.

  • 0 of 7 answers accepted...... Please get in the habit of **accepting** the best answer provided, the one solving your problem. It's the right and polite thing to do on StackOverflow and serves as an incentive for others to continue to help those looking for answers. See: http://meta.stackexchange.com/questions/5234/accepting-answers-what-is-it-all-about – marc_s Jan 18 '10 at 18:08
  • 1
    I presume that this is n error message and that you would like some help debugging, no? However, I'm having to guess here because you haven't actually written a question or request for help. Please clarify. – dmckee --- ex-moderator kitten Jan 19 '10 at 02:52

4 Answers4

12

What worked for me:

alter database [database_name] set offline

...wait a few seconds...

alter database [database_name] set online

This is better than restarting SQL Server since restarting SQL Server takes all databases offline (not just the database that is inaccessible).

Ladadadada
  • 26,337
  • 7
  • 59
  • 90
Robert van den Berg
  • 121
  • 1
  • 3
7

I encountered this same error today. Restarting the SQL Server service fixed it.

The SQL Server Error log and the Windows Event Log showed the same error:

The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000026000 in file 'blah.mdf'. Additional messages in the SQL Server error log and system event 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.

And:

Error: 823, Severity: 24, State: 2

After reading Robert van den Berg's answer, I would try taking the database offline and then online first if you have other databases that you need to keep online.

Paul Williams
  • 324
  • 2
  • 9
  • Also had to restart my SQL Server service in order to resolve this issue. – jpierson Dec 06 '16 at 16:27
  • A script to restart does not work, but when manually restarting the SQL service resolves the issue. Help will be appreciated – Rajesh Jun 19 '18 at 16:37
3

First read the logs that were indicated in the error message.

Then try resetting the server, then run the DBCC CheckDB again.

Paulo Santos
  • 173
  • 6
  • restart worked for me, but I don't think it is the best solution. Event logs and SQL logs did not shed light on what went wrong for me. – SimplyInk Oct 10 '16 at 03:31
0

In my case I was able to use

exec sp_detach_db [dbName];

followed by

exec sp_attach_db [dbName] , @filename1 = N'U:\mdf\dbName.mdf' , @filename2 = N'G:\ldf\dbName_log.ldf';

for 30 databases under SQL 2017. Even though the "detach" process threw an error, SQL did detach the database.

I used select db_name(database_id), * from sys.master_files before I started detaching, so I could script out the attach process, given I had 30 databases in this state.

I found the catalyst in my case. I had "extended" two volumes the night before. Disk Management had reported it could not extend the volume at that time. I had decided to wait for a maintenance window to try again, and there were no errors yet.

Five hours later a backup started that used VSS. I am convinced the combination of a failed "extend volume" and the VSS snapshot put the volumes in an unusual state.

John Chertudi
  • 41
  • 2