A union of curiosity and data science

Knowledgebase and brain dump of a database engineer

Disk Space Monitoring Procedure without OLE Automation Procedures

The procedure "Monitor_Disk_Space" was created to return more information than the widely used "capacity_DiskSpaceTracking" procedure which is used for monitoring.
 The capacity_diskspacetracking procedure can be found all over the web a simple google search will return many results.
Compare the output of two procedures: "capacity_DiskSpaceTracking"  and  "Disk_Space_Monitoring"

"monitor_disk_space"  - the procedure code for this is below.
 as you can see , we're gathering much more information in the new procedure without  enabling OLE Automation Procedures.
Procedure: Monitor_Disk_Space





create proc monitor_disk_space 



 -- 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 =


    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 



 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, 


    FROM #output 

    WHERE [output] LIKE 'Total # of %' 

   ) AS

 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 











Comments (1) -

  • Yyy7TP7b

    6/3/2019 11:43:23 PM | Reply

    833907 847172Youre so cool! I dont suppose Ive learn something like this before. So great to search out any person with some distinctive thoughts on this subject. realy thanks for starting this up. this web site is one thing that�s wanted on the net, somebody with a bit originality. useful job for bringing one thing new to the internet! 992100

Add comment