Drive Letter Case Sensitivity Causes Duplicate Volume Entries and PK Violations

[j005e]

Description

The current T-SQL query for collecting drive information from SQL Server DMVs fails to normalize drive letter cases, leading to duplicate volume entries and subsequent PRIMARY KEY violations during data insertion.

Current Behavior

• The query treats differently-cased drive letters (e.g., 'D:' and 'd:') as separate volumes
• This results in duplicate data collection for the same physical volume
• During insertion, this causes PRIMARY KEY violations as these represent the same underlying drive

Expected Behavior

• Drive letters should be case-insensitive in volume identification
• 'D:' and 'd:' should be treated as the same volume
• Only one record should be generated per unique physical volume

Technical Details

• Affects drive data collection from SQL Server sys.dm_os_volume_stats
• Issue occurs during the Drives Import phase

Reproduction Steps

1. Have a SQL Server instance with database files using mixed case Drive Letters (e.g., 'D:' and 'd:')
2. Run the DBADash Drives Import process using T-SQL (not WMI)
3. Observe PRIMARY KEY violation errors during data insertion

Proposed Solution

Modify the T-SQL query to normalize drive letter cases using UPPER() and DISTINCT when collecting volume information:

SELECT  DISTINCT UPPER(dovs.volume_mount_point) AS Name,
        AVG(dovs.total_bytes) as Capacity,
        AVG(dovs.available_bytes) as FreeSpace,
        dovs.logical_volume_name as Label
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) dovs
GROUP BY dovs.volume_mount_point,
        dovs.logical_volume_name;

[DavidWiseman]

Hi,

This is a good bug report - thanks! I suggest altering the solution to group by the mount point rather than introducing the DISTINCT.

SELECT  UPPER(dovs.volume_mount_point) AS Name,
	AVG(dovs.total_bytes) as Capacity,
	AVG(dovs.available_bytes) as FreeSpace,
	dovs.logical_volume_name as Label
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) dovs
GROUP BY UPPER(dovs.volume_mount_point),
		 dovs.logical_volume_name;

This bug would only impact case-sensitive collations where WMI isn't used. A workaround might be to change the initial catalog to a case-insensitive DB - but DBA Dash should handle it correctly regardless of the collation. We can hopefully get a fix in for the next release.

If you are interested, you could submit a pull request to fix the bug. If not I'm happy to do it.

Thanks!

David