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.

Hello Carolyn:

I had a question about one item in the article.  In a textbox on the report there is the following snippet of 'code' visible:

=First(Fields!......

What is that "First" all about?  Thx.  D Lewis

DavidL:

The First() function returns the first value found in a field of the dataset. Refer to the Books Online for more information.

I hope this helps.

Regards!

what permissions do you need to successfully run xp_fixeddrives on 2k5?  I find that a basic user ends up with an empty result set but no error messages!

The user would need probably need execute permissions on xp_fixeddrives.