I have recently created a solution for this for all servers (not just SQL). All that it requires is that you have WMI running on the servers that you wish to monitor. It makes use of the winmgmts object to gather the disk size and free space for all available logical disks.
STEP 1.
Pick a server where you want to record the monitoring and create a database called Monitor with tables and user name as follows:
Use Monitor
Go
Create table logtable (id int identity(1,1), notes varchar(1000), date datetime default getdate())
go
Create Table Servers(ServerName varchar(128))
go
Create Table FreeSpace(Computer varchar(128),
Drive varchar(2),DiskSize decimal(28,5)
,FreeSpace decimal(28,5),Percentage decimal (10,5), Date datetime)
go
use master
go
sp_addlogin 'diskuser','disk','Monitor'
go
use Monitor
go
sp_adduser 'diskuser'
go
sp_addrolemember 'db_datawriter','Diskuser'
go
sp_addrolemember 'db_datareader','Diskuser'
go
STEP 2 - insert the names of the servers you would like to monitor. For example:
Insert into Servers select 'SERVER1'
Insert into Servers select 'SERVER2' etc, etc....
Note: I have also added some columns to the server table for things like a server category and a primary and secondary support person.
STEP 3
Create a VBS script to get the data and insert into the tables. Here is a copy of my script, this will do some word wrapping so you will have to fix it in your script.
'Objective: Find Disk Free Space in all the listed servers in a table and write to a database table
on error resume next
Const MBCONVERSION= 1048576
Dim AdCn
Dim ErrorSQL
Dim AdRec
Dim i, SQL
Set AdCn = CreateObject("ADODB.Connection")
Set AdRec = CreateObject("ADODB.Recordset")
Set AdRec1 = CreateObject("ADODB.Recordset")
AdCn.Open = "Provider=SQLOLEDB.1;Data Source=YOURSERVER;Initial Catalog=Monitor;user id = 'diskuser';password='disk' "
SQL1 = "Select ServerName from Servers"
AdRec1.Open SQL1, AdCn,1,1
ErrorSQL="insert into logtable(notes) values ('Disk Monitoring Started')"
AdRec.Open ErrorSQL, AdCn,1,1
while not Adrec1.EOF
Computer = Adrec1("ServerName")
Set objWMIService = GetObject("winmgmts://" & Computer)
'wscript.echo err.number
If err.number <> 0 then
ErrorSQL="insert into logtable(notes) values ('" + Computer + ": Error-- " + Err.description+ "')"
AdRec.Open ErrorSQL, AdCn,1,1
else
Set colLogicalDisk = objWMIService.InstancesOf("Win32_LogicalDisk")
If err.number <> 0 then
ErrorSQL="insert into logtable(notes) values ('" + Computer + ": Error-- " + Err.description+ "')"
else
For Each objLogicalDisk In colLogicalDisk
if objLogicalDisk.drivetype=3 then
SQL = "Insert into FreeSpace (Computer,Drive,DiskSize,FreeSpace,Percentage,date) values('"_
&Computer&"','" & objLogicalDisk.DeviceID &"',"& objLogicalDisk.size/MBCONVERSION &_
"," & objLogicalDisk.freespace/MBCONVERSION &_
"," &((objLogicalDisk.freespace/MBCONVERSION)/(objLogicalDisk.size/MBCONVERSION))*100_
&",'" &now() &"')"
AdRec.Open SQL, AdCn,1,1
end if
Next
end if
end if
err.Clear
Adrec1.movenext
Wend
AdRec.Open "insert into logtable(notes) values ('Disk Monitoring - Completed')", AdCn,1,1
STEP 4
Save the above into a VBS file on your server where you created the database and schedule it to run using Windows Schedule. Make sure that if you are crossing domains that you pick a "run as" login for the Schedule that would have Admin rights to all the servers in the list.
I schedule mine to run once per day and have written a few ReportingService reports out of it. You could easily create a subscription to email a list of server results that drop below a certain percentage of free space.