We love xp_fixeddrives here, it’s a quick and simple way to see how much space you’ve got available on your drives. But there are just a couple of things that I really wish it would do better.
Firstly, I’d love to see the total size of the drive and possibly even a percentage of free space left.
Secondly, mount points. If you’ve got any databases that are on mount points, it’s not going to give you any idea of what you’ve got left on there.
This is the reason that I put together sp_drivespace. The following procedure uses dm_os_volume_stats, which will give us all the information that we’re missing in a simple to run procedure.
The query against dm_os_volume_stats was written by Adrian Buckman and has been pinched from the latest release of our Undercover Inspector.
Parameters
The proc has the following parameters
@xp_fixeddrivesCompat – Switching this to 1 will format the output of the procedure to match xp_fixeddrives. Useful if you’re using xp_fixeddrives and want a drop in replacement that’ll cover your mount points. Default value is 0.
@unit – Defines the unit of storage that will be output, BYTE, KB, MB, TB or GB. The default value is GB. This parameter is ignored if @xp_fixeddrivesCompat is on.
Code
The code can be found below or on our Git Hub site.
https://github.com/SQLUndercover/UndercoverToolbox/blob/master/sp_drivespace.sql
/******************************************************************
Author: David Fowler
Revision date: 3 July 2019
Version: 1
© www.sqlundercover.com
This script is for personal, educational, and internal
corporate purposes, provided that this header is preserved. Redistribution or sale
of this script,in whole or in part, is prohibited without the author's express
written consent.
The software is provided "as is", without warranty of any kind, express or
implied, including but not limited to the warranties of merchantability,
fitness for a particular purpose and noninfringement. in no event shall the
authors or copyright holders be liable for any claim, damages or other
liability, whether in an action of contract, tort or otherwise, arising from,
out of or in connection with the software or the use or other dealings in the
software.
******************************************************************/USE master
GO
CREATE PROC sp_drivespace
@xp_fixeddrivesCompat BIT = 0, --return results matching the format of xp_fixeddrives, allows use as a drop in replacement for xp_fixeddrives
@unit VARCHAR(4) = 'GB' --BYTE, KB, MB, GB or TB - ignored if @xp_fixeddrivesCompat = 1
AS
BEGIN
IF OBJECT_ID('tempdb..#driveinfo') IS NOT NULL
DROP TABLE #driveinfo
--check for valid unit value
IF @unit NOT IN ('BYTE','KB','MB','TB','GB')
RAISERROR (N'Invalid Unit Specified, Must Be BYTE, KB, MB, GB or TB' , 15, 1)
--set divisor, to be used when converting units
DECLARE @divisor BIGINT
SELECT @divisor =CASE @unit
WHEN 'BYTE' THEN 1
WHEN 'KB' THEN 1024
WHEN 'MB' THEN POWER(1024,2)
WHEN 'GB' THEN POWER(1024,3)
WHEN 'TB' THEN POWER(CAST(1024 AS BIGINT),4)
END
CREATE TABLE #driveinfo
(volume_mount_point NVARCHAR(512),
available_bytes BIGINT,
total_bytes BIGINT,
logical_volume_name NVARCHAR(512))
--DistinctDrives derived table updated to show all database_id and file_id combinations grouped by file path.
--Row number is applied so that we can filter just one database_id and file_id combination per file path and then these
--combinations are passed to the sys.dm_os_volume_stats system TVF , the reason for the filtering within the derived table is
--to reduce the number of executions performed by the TVF because on instances with lots of databases this can slow execution.
INSERT INTO #driveinfo (volume_mount_point,available_bytes,total_bytes,logical_volume_name)
SELECT DISTINCT
volumestats.volume_mount_point,
volumestats.available_bytes,
volumestats.total_bytes,
logical_volume_name
FROM
(
SELECT
[database_id],
[file_id],
ROW_NUMBER() OVER (PARTITION BY SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('',REVERSE(physical_name))+1)
ORDER BY SUBSTRING(physical_name,1,LEN(physical_name)-CHARINDEX('',REVERSE(physical_name))+1) ASC) AS RowNum
FROM sys.master_files
WHERE database_id IN (SELECT database_id FROM sys.databases WHERE state = 0)
) DistinctDrives
CROSS APPLY sys.dm_os_volume_stats([DistinctDrives].[database_id],[DistinctDrives].[file_id]) volumestats
WHERE DistinctDrives.RowNum = 1
IF (@xp_fixeddrivesCompat = 1)
BEGIN
--if @xp_fixeddrivesCompat, return results matching the format of xp_fixeddrives
SELECT volume_mount_point, available_bytes / 1024 / 1024 AS [MB free]
FROM #driveinfo
END
ELSE
BEGIN
SELECTvolume_mount_point,
CAST(CAST(available_bytes AS DECIMAL(20,2)) / @divisor AS DECIMAL(20,2)) AS Available,
CAST(CAST(total_bytes AS DECIMAL(20,2)) / @divisor AS DECIMAL(20,2)) AS Total,
CAST(available_bytes AS DECIMAL(20,2))/CAST(total_bytes AS DECIMAL(20,2)) * 100 AS PercentFree
FROM #driveinfo
END
END