August 22, 2016 at 4:41 am
SQL2012, after a server reboot getting the below error:
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x000000000c6000 in file 'X:\XXXXXXX\Data\DBYYYYYY.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.
Running DBCC CHECKDB results in the error:
Msg 2520, Level 16, State 5, Line 1
Could not find database 'XXXXXXXXX'. The database either does not exist, or was dropped before a statement tried to use it. Verify if the database exists by querying the sys.databases catalog view.
Querying the sys.databases catalog gives the same error regarding os error 21 again.
Anyone had this occur before? There my be no good backups….
...
August 22, 2016 at 5:06 am
Is drive X accessible? Can you see the files on it?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2016 at 5:22 am
Yes files and drives are accessible and look healthy.
...
August 22, 2016 at 5:24 am
Try restarting the SQL Server instance.
At the time it started, the drive was probably inaccessbile and SQL won't retry.
If that works, you need to investigate why the drive was slow to come online, and you definitely need to fix the 'no good backups' situation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2016 at 11:10 am
Gail
Many thanks, yes that did work. FYI what did not work is; take database offline, detach database, restore database and whatever else one would consider. All gave exactly the same error message so on the edge of using process explorer to see what was holding any files, the restart service instance worked!
I am going to guess and this is from memory, that it is the mapped drives that were the problem!
NB the backups were actually there and OK but when a restore was attempted, it still returned the same error.....backups are now continuing fine.
...
August 23, 2016 at 11:32 am
HappyGeek (8/23/2016)
I am going to guess and this is from memory, that it is the mapped drives that were the problem!
I would in general suggest staying away from putting SQL files (database files or otherwise) on mapped drives. Generally, unless you or your sysadmins have set something up, the mapped drives won't map until someone logs into the server.
Even if you've got something set up to map the drives without a logged in user (possible,) it takes time for the drives to map. I would expect the SQL service would be started before the drive mapping starts, which would result in the problem you had. I can see a way to work around it, but it would be an ugly, fragile, kludge that I wouldn't trust on a production system (creating a batch file or Powershell script to check for the existence of the mapped drive, then the script would either restart the SQL service or start it.)
Now, if the "mapped" drive is a SAN drive (for example, an iSCSI volume,) you have a different problem and should work with your sysadmins and SAN admins to resolve the issue.
Jason
August 23, 2016 at 12:04 pm
HappyGeek (8/23/2016)
NB the backups were actually there and OK but when a restore was attempted, it still returned the same error.....backups are now continuing fine.
Backups that are on the same drive as the data files are kinda useless. You generally want the backups to still be around if the data drive fails.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2016 at 12:35 pm
jasona.work (8/23/2016)
HappyGeek (8/23/2016)
I am going to guess and this is from memory, that it is the mapped drives that were the problem!I would in general suggest staying away from putting SQL files (database files or otherwise) on mapped drives. Generally, unless you or your sysadmins have set something up, the mapped drives won't map until someone logs into the server.
....
Now, if the "mapped" drive is a SAN drive (for example, an iSCSI volume,) you have a different problem and should work with your sysadmins and SAN admins to resolve the issue.
Jason
Would totally agree and did hit the problem of mapped drives years ago; am in a new (very recent) post and have inherited a setup that has some "interesting" quirks, but advised they have never been a problem (my first task was to enable DAC on all servers).
Appreciate the sanity check 🙂
...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply