Drive Letter

  • On one of the servers (Win 2000 SP4, SQL Server 2000 SP4) I have C:\, D:\ and E:\ drive letters, but when I try to attached a database that has an NDF file that I moved to D:\ it is failing (both GUI or script), with a drive invalid error.< ?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

    Also when I run xp_fixeddrives the D:\ is not displayed. Does anybody know what might be the issue?

    I try renaming the drive from d:\ to I:\, reboot the server,...no result.

     

  • D: must not be recognized as a hard (fixed) drive.  SQL Server will not let you put database files on a networked mapped drive.  So if it is not in xp_fixeddrives, it must assume that d: is a networked drive.  Is it a mapped drive? Or possibly a Subst drive?

    Hope this helps



    Mark

  • It is a local drive.

  • Are you sure?  SQL server doesn't think so.

    More specifically, the Windows OS does not think so.  SQL Server will use WinAPI GetDriveType (see this MSDN article) to determine if it can use this drive.  If it doesn't say DRIVE_FIXED or DRIVE_CDROM then it will not allow a database file on the drive.

    (And, you can use SQL Server databases on CD drives.  It will be read-only, and is great for accessing archives.)

    Edit: It may allow some of the other drive types, I have not tested or tried those.  For instance, I would not be surprised that it may allow DRIVE_RAMDISK also.



    Mark

  • I am positive, here is the output of msinfo

    Drive C:

    Description Local Fixed Disk

    Compressed False

    File System NTFS

    Size 14.65 GB (15,726,702,592 bytes)

    Free Space 1.35 GB (1,448,914,944 bytes)

    Volume Name Local Disk

    Volume Serial Number 88C94C44

    Partition Disk #0, Partition #0

    Partition Size 14.65 GB (15,726,703,104 bytes)

    Starting Offset 32256 bytes

    Drive Description Disk drive

    Drive Manufacturer (Standard disk drives)

    Drive Model Maxtor 6Y080L0

    Drive BytesPerSector 512

    Drive MediaLoaded True

    Drive MediaType Fixed hard disk media

    Drive Partitions 3

    Drive SCSIBus 0

    Drive SCSILogicalUnit 0

    Drive SCSIPort 0

    Drive SCSITargetId 0

    Drive SectorsPerTrack 63

    Drive Size 79999073280 bytes

    Drive TotalCylinders 9726

    Drive TotalSectors 156248190

    Drive TotalTracks 2480130

    Drive TracksPerCylinder 255

      

    Drive D:

    Description Local Fixed Disk

    Compressed False

    File System NTFS

    Size 19.53 GB (20,974,428,160 bytes)

    Free Space 12.97 GB (13,922,160,640 bytes)

    Volume Name Applications

    Volume Serial Number E09C841D

    Partition Disk #0, Partition #1

    Partition Size 59.85 GB (64,264,112,640 bytes)

    Starting Offset Not Available

    Drive Description Disk drive

    Drive Manufacturer (Standard disk drives)

    Drive Model Maxtor 6Y080L0

    Drive BytesPerSector 512

    Drive MediaLoaded True

    Drive MediaType Fixed hard disk media

    Drive Partitions 3

    Drive SCSIBus 0

    Drive SCSILogicalUnit 0

    Drive SCSIPort 0

    Drive SCSITargetId 0

    Drive SectorsPerTrack 63

    Drive Size 79999073280 bytes

    Drive TotalCylinders 9726

    Drive TotalSectors 156248190

    Drive TotalTracks 2480130

    Drive TracksPerCylinder 255

      

    Drive E:

    Description Local Fixed Disk

    Compressed False

    File System NTFS

    Size 40.32 GB (43,289,612,288 bytes)

    Free Space 30.68 GB (32,941,727,744 bytes)

    Volume Name Data

    Volume Serial Number 181B5687

    Partition Disk #0, Partition #1

    Partition Size 59.85 GB (64,264,112,640 bytes)

    Starting Offset Not Available

    Drive Description Disk drive

    Drive Manufacturer (Standard disk drives)

    Drive Model Maxtor 6Y080L0

    Drive BytesPerSector 512

    Drive MediaLoaded True

    Drive MediaType Fixed hard disk media

    Drive Partitions 3

    Drive SCSIBus 0

    Drive SCSILogicalUnit 0

    Drive SCSIPort 0

    Drive SCSITargetId 0

    Drive SectorsPerTrack 63

    Drive Size 79999073280 bytes

    Drive TotalCylinders 9726

    Drive TotalSectors 156248190

    Drive TotalTracks 2480130

    Drive TracksPerCylinder 255

      

  • Is this a cluster ???

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • Another possibility just occurred to me.  Is either the D: volume, or the directory where you are trying to put the file, marked for compression?  SQL Server will also not allow a database file on a compressed volume.



    Mark

  • Just a regular server, no clustering involved, and no HD compression enabled.

    Anyway it looks like an anomaly, a reinstall of the OS/SQL Server might fix it. I just wanted to know if this is a normal SQL Server behavior in certain condition and what might be those conditions.

    Thanks anyway for your help.

     

  • Hi

    I'm not not quit sure about this issue, but are the NTFS permissions OK?

    JP

  • Looks okay to me. Should I look for something specific?

  • Check the account you use to start SQL Server service. Check the NTFS permissions on drive D. The account should be listed there (or via group membership) and should have read, write permissions.

    JP

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply