Monitoring Disk space using T-SQL and Powershell

UPDATE: New version with bug fixes. Now works on named instances !! (2014-10-14)

Hello everybody,

Here is a handy script that allows you to get disks information using T-SQL and Powershell. It is useful to monitor the free space on each disk so we can create a sql job to run it periodically and send out a notification when space is getting low

Here is the script

USE master

GO

SET NOCOUNT ON

declare @svrName varchar(255)

declare @sql varchar(400)

--by default it will take the current server name, we can the set the server name as well

set @svrName = cast(SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') as varchar(255))

set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace,Label | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''&''+$_.label+''*''}"'

--creating a temporary table

DECLARE @output TABLE

(line varchar(255))

--inserting disk name, total space and free space value in to temporary table

insert @output

EXEC xp_cmdshell @sql

DECLARE @DISKS TABLE(

id int identity

,[DiskName] varchar(10)

,[Capacity(MB)] bigint

,[FreeSpace(MB)] bigint

,[Label] varchar(200)

)

INSERT INTO @DISKS

select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename

      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'

      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,      (CHARINDEX('&',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)'

      ,rtrim(ltrim(SUBSTRING(line,CHARINDEX('&',line)+1,      (CHARINDEX('*',line) -1)-CHARINDEX('&',line)) )) as 'Label'

from @output

where line like '[A-Z][:]%'

order by drivename

select *

,[Capacity(MB)]/1024 as [Capacity(GB)],[FreeSpace(MB)]/1024 as [FreeSpace(GB)]

,round( [FreeSpace(MB)]*100/[Capacity(MB)],2) as [% Free]