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

    Question

  • Dear DBAs,

    A database backup job has failed for the last couple of days with below error. I see that there is no P drive for PP1DATA. When I tried to bring drives online to find P drive and bring it up I couldn't bring them online. Is the absence of P drive related to this error?

    Thank you,

     

    ." failed with the following error: "The operating system returned error 21(The device is not ready.) to SQL Server during a write at offset 0x0000005ec32000 in file 'P:\PP1DATA1\PP1DATA1.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.  BACKUP DATABASE is terminating abnormally."

    Wednesday, September 08, 2010 5:52 PM

Answers

  • Junior,

    Sort of.  I misunderstood what the P drive was, sorry about that.

    It seems that the backup failed because it could not find the MDF for the database it was trying to back up.  I am actually surprised that your instance was running at all.  Are your system databases on a different drive?

    I think that we're having a problem with some definitions here.  A 'shared drive' is not really the appropriate terminology.  In order for a SQL cluster to use a drive, that drive must be known to the cluster as a cluster resource.  Indeed, if the P drive failed over to the other node, then it would HAVE to be a cluster resource.  The easiest way to determine which drives are cluster resources is to look into the Cluster Administrator.  It will tell you which resources are active on which nodes. You can get to it via the administrative tools menu.  If you do not know the cluster name, you can simply use a period '.' if you are on the cluster when you run the Cluster Administrator.

    It is important to understand that a SQL failover cluster is designed to recover from a failure at the server level.  This specifically excludes storage.  If your storage medium fails, no amount of clustering will save you.

    As I mentioned earlier, you need to make sure that the SQL virtual server and all related drive resources are on the same node in order for your server to work as expected.  Correctly set dependencies will help with that

    -Dave

    • Edited by Dave Hearne Friday, September 10, 2010 5:10 PM typo
    • Proposed as answer by Dave Hearne Friday, September 10, 2010 5:10 PM
    • Marked as answer by JSQL DBA Saturday, September 11, 2010 1:31 AM
    Thursday, September 09, 2010 8:22 PM

All replies

  • Database PP1DATA is located on the P: drive and your backup job probably does a backup of all (user) databases. Since it cannot find the datafile for PP1DATA, it gives you this error.

    If you no longer need this database, you can detach it.

    Tom


    Tom Van Zele | Blog | Twitter | LinkedIn
    Wednesday, September 08, 2010 7:30 PM
  • PP1 is a clustered server instance. The P drive went to the other node on the clustered server.
    Wednesday, September 08, 2010 7:47 PM
  • And your sql services are not running on the other node?

    Tom


    Tom Van Zele | Blog | Twitter | LinkedIn
    Wednesday, September 08, 2010 7:54 PM
  • The SQL Server resource should be dependent upon ALL of the drive resources it uses.  That will prevent this particular problem from happening again.
    Wednesday, September 08, 2010 8:12 PM
  • Cluster uses a shared drive , so it does not matter on which node SQL Server is running and which node owns the disk drives.

    The error cleary means that backup got terminated due to MDF file being inaccessible .

    The win32 error code is :ERROR_NOT_READY .this may happen due to

    STATUS_NO_MEDIA_IN_DEVICE
    STATUS_VOLUME_DISMOUNTED
    STATUS_DEVICE_POWERED_OFF
    STATUS_DEVICE_OFF_LINE
    STATUS_DEVICE_NOT_READY
    STATUS_DEVICE_POWER_FAILURE

    check the system and ap logs on the nodes and you will find more device related errors or network errors as well.

    Regards 


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Thursday, September 09, 2010 4:32 AM
  • Yes. The other node was not running.
    Thursday, September 09, 2010 2:57 PM
  • We moved P drive to PP1 instance and our backup job succeeded. I didn't verify P drive is a shared drive. I have to check. Thank you for your help.

    Thank you all who responded to my post. May your kindness be blessed.

     

    Thursday, September 09, 2010 3:00 PM
  • Cluster uses a shared drive , so it does not matter on which node SQL Server is running and which node owns the disk drives.


    Your comment about 'shared drives' is not correct.  Clustering Services clusters utilized the 'shared nothing' methodology, meaning that a resource is owned by exactly one machine, or node, at a time to prevent contaminated data.  As such, a drive 'owned' by one node cannot be seen or utilized by another node.

    -Dave

    • Edited by Dave Hearne Thursday, September 09, 2010 8:26 PM read original incorrectly
    Thursday, September 09, 2010 3:07 PM
  • The backup in PP1 instance didn't work because one of its drives P moved to PE1 instance which is the other node of the clustered server. According to what you said P drive is not a shared drive. If it were PP1 backup should have worked.

     

     

    Thursday, September 09, 2010 3:24 PM
  • I'm not sure what you are talking about Junior.

    Let me lay out a simplified example for you:

    N1 = Node 1 of cluster C

    N2 = Node 2 of cluster C

    P = drive resource

    Q = drive resource

    S = Virtual Server

    Normally, you would have all of the resources (S, P, and Q) on the same node, say N1.  If there is a problem with N1, all the resources would roll over to N2.  The server remains available because it is accessed via the cluster name, C

    Now, if you manually fail the P drive from N1 to N2, and leave S on N1, S will not be able to see or access P in any way.  This is a result of Microsoft's 'shared nothing' approach to clustering. 

    In most cases, you would want to have the data and log drives a dependencies for the SQL virtual server.  In your case, we're talking about a backup drive, so it makes sense to not include it as a dependency.  However, if it WAS set up as a dependency, you would not have this issue. As long as the P: drive is on the same node as the virtual server, you should be OK

    -Dave

    Thursday, September 09, 2010 3:54 PM
  •  

    Dave, You said, "In most cases, you would want to have the data and log drives a dependencies for the SQL virtual server.  In your case, we're talking about a backup drive, so it makes sense to not include it as a dependency.  However, if it WAS set up as a dependency, you would not have this issue. As long as the P: drive is on the same node as the virtual server, you should be OK."

     

    P drive in PP1 instance is for data. When the backup failed in PP1 instance I saw a drive for log in PP1 but a drive for data (P drive) was missing. After P drive came back to PP1 the backup ran successfully. This sounds that P is not a shared drive. I just verified that there is a drive for another node PE1 which is J drive. For some reason when the a failover occurred P drive moved to PE1 the PP1 backup failed. Does this make a sense?

    Thursday, September 09, 2010 4:19 PM
  • Junior,

    Sort of.  I misunderstood what the P drive was, sorry about that.

    It seems that the backup failed because it could not find the MDF for the database it was trying to back up.  I am actually surprised that your instance was running at all.  Are your system databases on a different drive?

    I think that we're having a problem with some definitions here.  A 'shared drive' is not really the appropriate terminology.  In order for a SQL cluster to use a drive, that drive must be known to the cluster as a cluster resource.  Indeed, if the P drive failed over to the other node, then it would HAVE to be a cluster resource.  The easiest way to determine which drives are cluster resources is to look into the Cluster Administrator.  It will tell you which resources are active on which nodes. You can get to it via the administrative tools menu.  If you do not know the cluster name, you can simply use a period '.' if you are on the cluster when you run the Cluster Administrator.

    It is important to understand that a SQL failover cluster is designed to recover from a failure at the server level.  This specifically excludes storage.  If your storage medium fails, no amount of clustering will save you.

    As I mentioned earlier, you need to make sure that the SQL virtual server and all related drive resources are on the same node in order for your server to work as expected.  Correctly set dependencies will help with that

    -Dave

    • Edited by Dave Hearne Friday, September 10, 2010 5:10 PM typo
    • Proposed as answer by Dave Hearne Friday, September 10, 2010 5:10 PM
    • Marked as answer by JSQL DBA Saturday, September 11, 2010 1:31 AM
    Thursday, September 09, 2010 8:22 PM