October 10, 2006 at 10:57 am
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.
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.
October 10, 2006 at 12:42 pm
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
October 10, 2006 at 1:18 pm
It is a local drive.
October 10, 2006 at 2:28 pm
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
October 11, 2006 at 2:05 pm
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
October 11, 2006 at 4:23 pm
Is this a cluster ???
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
October 12, 2006 at 5:55 am
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
October 12, 2006 at 9:14 am
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.
October 13, 2006 at 4:55 am
Hi
I'm not not quit sure about this issue, but are the NTFS permissions OK?
JP
October 13, 2006 at 6:27 am
Looks okay to me. Should I look for something specific?
October 16, 2006 at 12:32 am
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply