/*
Checking contents and size of filesystems
*/

SELECT file_or_directory_name
, level, is_directory, creation_time, (size_in_bytes /1024 ) as [Size_in_KB], (size_in_bytes /1024/1024/1024 ) as [Size_in_GB]
FROM sys.dm_os_enumerate_filesystem(N’R:\SQLServerBackups\LDESQLDBUAT002\’, N’*.*’)
order by level, creation_time desc

/* Other methods to check contents of filesystems

SELECT *
FROM OPENROWSET(BULK ‘R:\SQLServerBackups\*’, FORMAT = ‘diff’) AS FileList;
go

EXEC master..xp_dirtree ‘R:\SQLServerBackups\eu-dave-sqldb-prd’, 1, 1
go

*/

/* To find free space on the Drives:
SELECT DISTINCT
dovs.volume_mount_point AS Drive,
CAST(dovs.total_bytes / 1048576.0 / 1024.0 AS DECIMAL(10, 2)) AS TotalSize_GB,
CAST(dovs.available_bytes / 1048576.0 / 1024.0 AS DECIMAL(10, 2)) AS FreeSpace_GB,
CAST(dovs.available_bytes * 100.0 / dovs.total_bytes AS DECIMAL(10, 2)) AS PercentFree
FROM
sys.master_files mf
CROSS APPLY
sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs;
*/

/* For free space on all drives
SELECT
fixed_drive_path AS [Drive],
drive_type_desc AS [Type],
CAST(free_space_in_bytes / 1024.0 / 1024 / 1024 AS NUMERIC(18,2)) AS [Free_GB]
FROM sys.dm_os_enumerate_fixed_drives
go
*/