Monitor Disk Space on Multiple SQL Servers

Monitor Disk Space on Multiple SQL Servers

In the typical IT department, an un-avoidable task is to monitor the disk space on all drives on certain servers. In addition, the methods presented here will help in monitoring the growth of files. In this article, I am going to discuss three different ways to monitor disk space on a list of servers and store the output either in a .CSV file or on a database table.

Method 1:

Check the drive space on all of the servers listed in a text file and create a .csv output file with the Server name, Drive names, Disk Space and Free space.

Let's say we have 3 servers and need to monitor the disk space on all of the hard disk drives on those servers.

Step1: Create a text file c:\ computerlist.txt with a list of server names

Example:

SQL2K
YUKON
DOTNET

Step2: Copy and paste the code below into c:\DiskSpacetoCSV.vbs

'Objective: Find Disk Free Space in all the listed servers and write to a .csv file

'Author: MAK

'Contact: mak_999@yahoo.com

Set iFSO = CreateObject("Scripting.FilesyStemObject")

Set oFSO = CreateObject("Scripting.FilesyStemObject")

InputFile="c:\computerlist.txt"

Outputfile="c:\Freespacelist_" + cstr(Month(now()))+"_"+cstr(day(now()))+".csv"

Set ofile = ofso.createTextFile(OutputFile, True)

Set ifile = iFSO.OpenTextFile(inputfile) 

Const MBCONVERSION= 1048576 

ofile.writeline "Computer,Drive,Disk Size,FreeSpace,%"

Do until ifile.AtEndOfLine

Computer       = ifile.ReadLine

Set objWMIService = GetObject("winmgmts://" & Computer) 

Set colLogicalDisk = objWMIService.InstancesOf("Win32_LogicalDisk") 

For Each objLogicalDisk In colLogicalDisk 

   if objLogicalDisk.drivetype=3 then

ofile.writeline Computer & "," & objLogicalDisk.DeviceID &_

 "," &  objLogicalDisk.size/MBCONVERSION & "," &_

 objLogicalDisk.freespace/MBCONVERSION & "," &_

 ((objLogicalDisk.freespace/MBCONVERSION)/(objLogicalDisk.size/MBCONVERSION))*100

   end if

Next 

Loop

Step3: Execute c:\DiskSpacetoCSV.vbs

When we execute this VB script, it will create a file c:\Freespacelist_12_27.csv that will contain details similar to those below.