CREATE PROCEDURE [ dbo ] . [ CheckDBFreeDiskSpace ]

@DatabaseName SYSNAME = NULL -- specify a particular database to check, otherwise NULL will check all databases

AS

BEGIN

/*

Description: Returns a list of available disk space in megabytes by volume. A volume

can be a fixed disk (drive letter) or a mounted folder volume. Only volumes that contain

database files are checked for the specified @DatabaseName or all databases (where @DatabaseName

is NULL)

Process Flow

- Get a list of database file paths

- Get a list of fixed disk volumes with xp_fixeddrives

- Filter unwanted fixed disk volumes

- Get a list of mounted volumes for each remaining fixed drive with dir /AL

- Filter unwanted mounted volumes

- Set which database paths exist on mounted volumes

- Filter redundant fixed disk volumes where all database file paths are represented by mount point volumes

- Merge the fixed disk volume and mount point volume lists

- Fetch free disk space for mounted volumes

- Report free disk space for all relevant volumes

Dependencies:

xp_cmdshell must be enabled on the server

SQL Service account must have permission to execute fsutil via xp_cmdshell

*/

DECLARE

@volume VARCHAR ( 260 ) -- a fixed disk or mount point volume

, @cmd VARCHAR ( 300 ) -- xp_cmdshell dynamic command

, @return_Value INT -- xp_cmdshell return value

-- list of databases to check disk space

DECLARE @t_Databases TABLE (

[ DATABASE_NAME ] SYSNAME NOT NULL

, [ DATABASE_ID ] INT NOT NULL

)

-- store the paths of any user database files found on the server

DECLARE @t_DatabaseFilePaths TABLE (

[ PATH ] VARCHAR ( 260 )

, [ IS_MOUNTED_VOLUME ] BIT NOT NULL DEFAULT ( 0 )

)

-- temporarily store information from xp_fixeddrives

DECLARE @t_FixedDrives TABLE (

[ VOLUME ] VARCHAR ( 3 )

, [ MB_FREE ] INT

)

-- temporarily store each mount point for a specific fixed drive

DECLARE @t_MountPoints TABLE (

[ MOUNT_PO IN T ] VARCHAR ( 255 )

)

-- store the mounted folder volumes

DECLARE @t_MountedVolumes TABLE (

[ VOLUME ] VARCHAR ( 260 )

)

-- store the merged list of fixed drive volumes and mounted folder volumes after filters

DECLARE @t_DiskFreeSpace TABLE (

[ VOLUME ] VARCHAR ( 260 )

, [ MB_FREE ] INT

)

-- store directory listing information from a dir command executed via xp_cmdshell

DECLARE @t_DirectoryListingFromDOS TABLE (

FileFN VARCHAR ( 7000 ) NULL

)

INSERT INTO @t_Databases ( [ DATABASE_NAME ] , [ DATABASE_ID ] )

SELECT

name AS [ DATABASE_NAME ]

, database_id AS [ DATABASE_ID ]

FROM

master. sys . databases

WHERE

state_desc = 'ONLINE'

IF ( @DatabaseName IS NOT NULL )

DELETE FROM @t_Databases WHERE [ DATABASE_NAME ] <> @DatabaseName

IF NOT EXISTS ( SELECT 1 FROM @t_Databases )

BEGIN

RAISERROR ( 'Database %s is not valid' , 16 , 1 , @DatabaseName )

RETURN - 1

END

-- Get a list of database file and recent backup file paths

INSERT INTO @t_DatabaseFilePaths ( [ PATH ] )

SELECT DISTINCT

LEFT ( bmf. physical_device_name , LEN ( bmf. physical_device_name ) - CHARINDEX ( '\' , REVERSE ( bmf. physical_device_name ) ) + 1 ) AS [ PATH ]

-- physical_device_name

FROM

msdb. dbo . BackupMediaFamily bmf

JOIN msdb. dbo . BackupSet bs

ON bmf. media_set_id = bs. media_set_id

JOIN @t_Databases d

ON bs. database_name = d. [ DATABASE_NAME ]

WHERE

bmf. physical_device_name LIKE '_:\%' -- must be a local file, not a network share or device name

AND bs. backup_finish_date > GETDATE ( ) - 2 -- last 2 days only (for relevant backup paths)

UNION

SELECT DISTINCT

LEFT ( saf. [ filename ] , LEN ( saf. [ filename ] ) - CHARINDEX ( '\' , REVERSE ( saf. [ filename ] ) ) + 1 ) AS [ PATH ]

FROM

master. sys . sysaltfiles saf

JOIN @t_Databases d

ON saf. dbid = d. [ DATABASE_ID ]

-- Get a list of fixed disk volumes with xp_fixeddrives

INSERT INTO @t_FixedDrives EXEC master. dbo . xp_fixeddrives

-- Convert single drive letters to paths

UPDATE @t_FixedDrives SET [ VOLUME ] = [ VOLUME ] + ':\'

-- Filter unwanted fixed disk volumes that are not like a database path

DELETE FROM

fd

FROM

@t_FixedDrives fd

WHERE

NOT EXISTS (

SELECT [ PATH ] FROM @t_DatabaseFilePaths WHERE [ PATH ] LIKE fd. [ VOLUME ] + '%'

)

-- Get a list of mounted volumes for each remaining fixed drive with dir /AL

WHILE EXISTS ( SELECT 1 FROM @t_FixedDrives )

BEGIN

-- Get the fixed drive volume to fetch mounted volumes for and insert the fixed drive volume into the final list

SELECT TOP 1 @volume = [ VOLUME ] FROM @t_FixedDrives

INSERT INTO @t_DiskFreeSpace ( [ VOLUME ] , [ MB_FREE ] ) SELECT [ VOLUME ] , [ MB_FREE ] FROM @t_FixedDrives WHERE [ VOLUME ] = @volume

DELETE FROM @t_FixedDrives WHERE [ VOLUME ] = @volume

-- Get the mount point volume information

SELECT @cmd = 'dir /AL /b /s "' + @volume + '"' -- /AL = junction points, /b = directories only, /s = include all sub-directories

INSERT INTO @t_MountPoints ( [ MOUNT_PO IN T ] ) EXEC @return_Value = master. dbo . xp_cmdshell @cmd

IF ( @return_Value = 0 ) -- only process when there is no error

BEGIN

DELETE FROM @t_MountPoints WHERE [ MOUNT_PO IN T ] IS NULL

INSERT INTO @t_MountedVolumes ( [ VOLUME ] ) SELECT [ MOUNT_PO IN T ] + '\' FROM @t_MountPoints

END

DELETE FROM @t_MountPoints

END

-- Filter unwanted mounted volumes

DELETE FROM

mv

FROM

@t_MountedVolumes mv

WHERE

NOT EXISTS (

SELECT [ PATH ] FROM @t_DatabaseFilePaths WHERE [ PATH ] LIKE mv. [ VOLUME ] + '%'

)

-- Set which database paths exist on mounted volumes

UPDATE

dfp

SET

[ IS_MOUNTED_VOLUME ] = 1

FROM

@t_DatabaseFilePaths dfp

JOIN @t_MountedVolumes mv

ON dfp. [ PATH ] LIKE mv. [ VOLUME ] + '%'

-- Filter redundant fixed disk volumes where all database file paths are represented by mount point volumes

DELETE FROM

dfs2

FROM

@t_DiskFreeSpace dfs2

WHERE

NOT EXISTS (

SELECT DISTINCT

dfs. [ VOLUME ]

FROM

@t_DiskFreeSpace dfs

JOIN @t_DatabaseFilePaths dfp

ON dfp. [ PATH ] LIKE dfs. [ VOLUME ] + '%'

WHERE

dfp. [ IS_MOUNTED_VOLUME ] = 0

)

-- Merge the fixed disk volume and mount point volume lists

INSERT INTO @t_DiskFreeSpace ( [ VOLUME ] , [ MB_FREE ] ) SELECT [ VOLUME ] , NULL FROM @t_MountedVolumes

WHILE EXISTS ( SELECT 1 FROM @t_DiskFreeSpace WHERE [ MB_FREE ] IS NULL )

BEGIN

-- strip the trailing slash or fsutil will fail

SELECT TOP 1 @volume = LEFT ( [ VOLUME ] , LEN ( [ VOLUME ] ) - 1 ) FROM @t_DiskFreeSpace WHERE [ MB_FREE ] IS NULL

SELECT @cmd = 'fsutil volume diskfree "' + @volume + '"'

INSERT INTO @t_DirectoryListingFromDOS EXEC master. dbo . xp_cmdshell @cmd

UPDATE

@t_DiskFreeSpace

SET

[ MB_Free ] = (

SELECT

( CONVERT ( BIGINT , RIGHT ( FileFN, LEN ( FileFN ) - CHARINDEX ( ':' , FileFN ) - 1 ) ) / 1048576 ) AS [ MB_Free ]

FROM

@t_DirectoryListingFromDOS

WHERE

LEFT ( IS NULL ( FileFN, '' ) , 27 ) = 'Total # of avail free bytes'

)

WHERE

[ VOLUME ] = @volume + '\'

DELETE FROM @t_DirectoryListingFromDOS

END

SELECT * FROM @t_DiskFreeSpace

RETURN 0

END