Sunday late night, I got a call stating that one of the warehouse databases was throwing this error while the Application job was running.
"Microsoft OLE DB Provider for SQL Server:
I/O error 21(The device is not ready.) detected during read at offset 0x0000a7a239c000 in file 'DataFileName'.
- HY000 - HRESULT=0x80004005 “
Since the error message mentions that the drive on which one of the data file resided was not ready, it was obvious that the underlying storage was the problem. But the System Event Log did not have any error messages related to storage. The database was online and accessible. But when the DBA tried accessing the Properties of this database, the same error was thrown. But queries like sp_helpfile and select * from sysobjects were executing successfully. When any query tried accessing the objects located on that particular data file, they would fail returning the drive error.
The reason for this error was not something that happened at that time. Another email chain from the Windows Administrators sent a few hours ago said it all. They had found that the drive letter associated with the Mount Point hosting this data file was missing and they had *mapped* it somehow. Not sure how they had done the drive mapping. Most likely the drive hosting this data file got disconnected while the database was online.
This issue was not that serious to make the database go into suspect mode but came into picture only when someone tried accessing the objects on that data file. Since the alerts on this server were not getting delivered due to an issue with SQLMail about which I had posted sometime back.
How to fix this issue? The easiest option is to take the database offline and bring it online. Since I had bad experiences with storage on these data warehouse servers, I was worried if some other mount points hosting these databases had similar issues. Hence it was decided to restart the SQL Server services. The database came online and the *device not ready* error message disappeared. I was happy that my worst fears did not come true and started my Monday on a good note.