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]
