Friday, April 1, 2016

Error 21.The device is not ready in SQL Server error log file.

It was Friday, evening shift, I got a ticket of Backup job failure on my production database. I login to that server and checked the Job history It was failing due to compression error. I checked the version etc and everything was fine. I decide to take a differential backup with script. It also got failed with error "BACKUP DATABASE is terminating abnormally" 


Although this was not related to space issue but still i checked for disk space and there was plenty of free space. I decide to check the error log file and there it was....

"The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000e56000 in file 'H:\MSSQL\Data\XXX.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."

Since the error message says that the drive on which data file resided was not ready, it was too obvious that there was some storage problem. I checked the system event log but did not found any error related to disk. The database was online and accessible. But when I tried to access the Properties of the databases, the same error was thrown. I was not able to see the user tables under the databases. But the other queries like sp_helpfile and select * from sysobjects were executing successfully. When I tried to access a user table by script it was failing with the same error.

I sent an email to storage team and they confirmed that everything was fine from there side.

There were two options with me to fix this issue at that time. 1: Take the database offline and bring it online. 2: Another was restart the SQL server. There were more than one databases on my SQL server instance so I restarted the SQL server service. The database came online and the *device not ready* error message disappeared. 

The next action you can take is to run DBCC on your database.

Thanks!!

6 comments:

  1. Great Share! I have also found a good article for SQL Server Error 823 http://www.sqlrecoverytool.com/fix-sql-server-fatal-error-823.html

    ReplyDelete
  2. Thanks (2) worked for me. I have a temporary database on an external drive that I use for Development and sometimes forget to detach it before I unplug everything.

    ReplyDelete
  3. Not ready to Start MS SQL Server? Contact to Microsoft SQL Server Support
    One of MS SQL Server client grumbles that, he effectively downloads and introduces the MS SQL Server however shockingly he can't make sense of how to run it. He is additionally not ready to run his inquiries because of some reason. As a matter of fact this occurs with the vast majority of the clients. Be that as it may, simply pause; we have best choice to take care of this issue by Cognegic's Remote Infrastructure Management Support for Microsoft SQL Server and Online MS SQL Server Support. We manage these sorts of specialized hiccups superbly.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete
  4. Troubleshoot if "Can't Connect to My Database" issue in MS SQL Server? Contact to Online MS SQL Server Support

    On the off chance that constantly you are confronting this issue and not ready to associate with your database then the main thing you need to do, check what adaptation is your SQL Server database? Cognegic bolsters the most current form of SQL Server. In the event that you presume any issues at that point specifically contact to our Microsoft SQL Server Support or Remote Infrastructure Management Support for Microsoft SQL Server. Cognegic's essential capacity is for secure information by giving first rate help.

    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    ReplyDelete