Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to work out a way of listing the drives that a particular instance of SQL Server is "aware" of, and understand the process.

I can run EXEC master..xp_fixeddrives which gives me a list of drives, but when I look in the GUI (Eg. Databases | Restore Database | From device | Add ) the list of available drives is far fewer.

By what process does SQL server become "aware" of a drive and is there any way to problematically determine the drives it is aware of?

Thanks in advance!

share|improve this question

2 Answers 2

up vote 4 down vote accepted

I'm guessing you're actually running on a clustered instance.

EXEC master..xp_fixeddrives is returning all the drives that the underlying OS is aware of, so a lot aren't available to your cluster instance

use:


SELECT * FROM sys.dm_io_cluster_shared_drives

instead, which will just list the ones available to your instance

share|improve this answer
    
Yes, you're right. this gives me exactly what I was looking for. Is there any other occasion when 'EXEC master..xp_fixeddrives' would return drives that SQL server couldn't access? for example if the SQL server service didn't have permissions to access them? –  reticentKoala Oct 11 '12 at 11:50
    
Can't think of any off the top of my head. xp_fixeddrives is 'undocumented' so noone really knows. I find it will either list everything the underlying server can see wether SQL can use them or not, or refuse to show anything because the calling user doesn't have the correct rights at the SQL or OS level. –  Stuart Moore Oct 11 '12 at 13:38

SQL Server 2008 R2 SP1 and SQL Server 2012 add a new function, sys.dm_os_volume_stats, which - unlike xp_fixeddrives - is documented and supported (and includes more useful information as well). The catch is that it will only return information for drives that have at least one data or log file on them, since it takes database_id and file_id as input:

SELECT d.* FROM sys.master_files AS f 
  CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS d;

Result set:

database_id                 int
file_id                     int
volume_mount_point          nvarchar(256)
volume_id                   nvarchar(256)
logical_volume_name         nvarchar(256)
file_system_type            nvarchar(256)
total_bytes                 bigint
available_bytes             bigint
supports_compression        tinyint
supports_alternate_streams  tinyint
supports_sparse_files       tinyint
is_read_only                tinyint
is_compressed               tinyint
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.