sys.dm_os_volume_stats problem

• stijn.wynants

  SSC Enthusiast

  Points: 128

  More actions

  March 25, 2015 at 6:46 am

  #295478

  Today I have been having quite an interesting issue, which I can't seem to fix. I am getting drive information for a Failover Cluster Instance SQL Server 2012. These drives are Volume Mount Points. For about 40 instances this runs perfectly except for one FCL this does not retrieve the right drive.

  The query I use is

  select

  case when type_desc = 'ROWS' then 'DATA' when type_desc = 'LOG' then 'LOG' else 'Unknown' end as DataOrLog,

  mf.database_id,

  mf.name,

  mf.physical_name,

  Drive.volume_mount_point,

  Drive.logical_volume_name

  from sys.master_files mf

  CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) Drive

  where mf.database_id not in (1,2,3,4)

  The normal result I get for this query is.

  

  This is the result I get with the problem case.

  

  What can be the reason that sys.dm_os_volume_stats can't find the right volume?

  Is it some wrong configuration at FCL level?

  Why does my sys.master_files show the right drive but the cross applied fileID to sys.dm_os_volume_stats not ?

  How does this JOIN work?