SQL Server query to get storage info

by Vlad Drumea July 22, 2024

written by Vlad Drumea July 22, 2024 0 comments

This post contains a short SQL Server query to get storage info for the disks hosting your instance’s database files without having to RDP into its host VM.

The query

SELECT DISTINCT [vs].[volume_mount_point]                                               AS [Disk],
                CAST([vs].[total_bytes] / 1024.0 / 1024.0 / 1024.0 AS DECIMAL(23, 2))   AS [DiskSize(GB)],
                CAST(( [vs].[available_bytes] / 1048576.0 ) / 1024.0 AS DECIMAL(23, 2)) AS [DiskFreeSpace(GB)],
                CAST(CAST( (([vs].[available_bytes] / 1048576.0 ) 
                / ( [vs].[total_bytes] / 1024.0 / 1024.0 )) * 100 AS DECIMAL(23, 2))
                AS NVARCHAR(25)) + N'%'                                                 AS [DiskFreePercent]
FROM   sys.[master_files] AS [mf]
       CROSS APPLY sys.dm_os_volume_stats([mf].[database_id], [mf].[file_id]) AS [vs]
ORDER  BY [DiskFreePercent] ASC;

SELECT DISTINCT [vs].[volume_mount_point] AS [Disk],

CAST([vs].[total_bytes] / 1024.0 / 1024.0 / 1024.0 AS DECIMAL(23, 2)) AS [DiskSize(GB)],

CAST(( [vs].[available_bytes] / 1048576.0 ) / 1024.0 AS DECIMAL(23, 2)) AS [DiskFreeSpace(GB)],

CAST(CAST( (([vs].[available_bytes] / 1048576.0 )

/ ( [vs].[total_bytes] / 1024.0 / 1024.0 )) * 100 AS DECIMAL(23, 2))

AS NVARCHAR(25)) + N'%' AS [DiskFreePercent]

FROM sys.[master_files] AS [mf]

CROSS APPLY sys.dm_os_volume_stats([mf].[database_id], [mf].[file_id]) AS [vs]

ORDER BY [DiskFreePercent] ASC;

Since it relies on the dm_os_volume_stats DMV, you’ll need VIEW SERVER STATE permission on SQL Server 2019 or VIEW SERVER PERFORMANCE STATE on 2022.

Output example

The column names are self-explanatory so there’s no need for me to go over them.

SSMS result set |Disk | DiskSize(GB) | DiskFreeSpace(GB) | DiskFreePercent| |E:\ | 875.61 | 215.75 | 24.64%| |F:\ | 838.35 | 467.66 | 55.78%| |C:\ | 837.62 | 479.78 | 57.28%| SQL Server query storage disk

Note that this will only return information about disks that have database files “living” on them.
If you want to get the free space for all the drives on the host you can just run:

EXEC xp_fixeddrives;

1	EXEC xp_fixeddrives;

Conclusion

This is a pretty simple query to get SQL Server storage info.
If you’ve found this useful you might also like this script to get database size info

Cookie	Duration	Description
cookielawinfo-checbox-analytics	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checbox-functional	11 months	The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checbox-others	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-necessary	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-performance	11 months	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy	11 months	The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.

SQL Server query to get storage info

The query

Output example

Conclusion

Export XML files from SQL Server using PowerShell

Create a SQL Server container with Podman

You may also like

Leave a Comment Cancel Reply