create proc monitor_disk_space
as
begin
-- CREATED BY Don Rickman
-- 9/1/2007
create table #disk_usage (
drive char(1)
, free_space_mb float
, total_space_mb float
, isShared bit)
insert into #disk_usage (drive, free_space_mb)
exec master..xp_fixeddrives
create table #output (drive char(1), [output] varchar(256))
declare @drive char(1), @cmd varchar(256), @t bigint, @u bigint
declare drive_cursor cursor for select drive from #disk_usage
open drive_cursor
fetch next from drive_cursor into @drive
while @@fetch_status = 0
begin
set @cmd = 'fsutil volume diskfree ' + @drive + ':'
insert into #output ([output]) EXEC XP_CMDSHELL @cmd
update #output set drive = @drive where drive is null
fetch next from drive_cursor into @drive
end
close drive_cursor
deallocate drive_cursor
SELECT drive,
cast(SUM(CASE WHEN [output] LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING([output], 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) / 1024.00 / 1024 / 1024 as decimal(10,2)) AS total_gigabytes,
cast(SUM(CASE WHEN [output] LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING([output], 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) / 1024.00 / 1024 / 1024 as decimal(10,2))AS avail_free_gigabytes
, case when s.drivename is not null then 1 else 0 end is_shared_drive
, sa.name
, sa.[filename] [file_name]
, CASE
WHEN sa.groupid = 1 THEN 'data'
WHEN sa.groupid = 0 THEN 'log'
END as 'file_type'
, cast( cast(sa.size as bigint) * 8192 / 1024.00 / 1024.00 / 1024.00 as decimal(10,3)) file_size_gigabytes
into #temp
FROM (
SELECT Drive,
[output]
FROM #output
WHERE [output] LIKE 'Total # of %'
) AS d
left join master.dbo.sysaltfiles sa on left(sa.filename,1) = d.drive
left join ::fn_servershareddrives() s on s.drivename COLLATE SQL_Latin1_General_CP1_CI_AS = d.drive
GROUP BY Drive, s.drivename, sa.size, sa.name, sa.filename, sa.groupid
select @@SERVERNAME [server]
, drive
, total_gigabytes
, avail_free_gigabytes
, cast( ((avail_free_gigabytes/ total_gigabytes)*100) as decimal(10,2) ) [percent_avail_disk_space]
, is_shared_drive
, name
, [file_name]
, file_type
, file_size_gigabytes
, getdate() [date]
from #temp
ORDER BY Drive, name
end
GO