Monitor Disk Space on Multiple SQL Servers

by MAK [Muthusamy Anantha Kumar]

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.


Computer, Drive, Disk Size, FreeSpace, %
SQL2K,C:,4000.52734375,1248.265625,31.2025270105992
SQL2K,F:,5765.48046875,4271.1875,74.0820738731255
SQL2K,L:,55003.765625,54937.61328125,99.8797312456732
SQL2K,M:,59467.13671875,45513.90625,76.536232886507
YUKON,C:,18998.7109375,15492.49609375,81.5449855767353
YUKON,D:,19155.59765625,17229.18359375,89.9433361617331
DOTNET,C:,3999.9765625,1432.2578125,35.8066551171198
DOTNET,D:,5771.8515625,3308.375,57.3191282585067

Method 2:

Check the drive space on all of the servers listed in a text file and store the output like Server name, Drive Name, Disk Space and Free space information on a SQL Server table.

Let’s say we have 3 servers and need to monitor the free space on those servers.

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

Example:

SQL2K
YUKON
DOTNET

Step2: Create database, table, Login and users in SQL Server.

Create Database DiskMonitor
go
use DiskMonitor
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','DiskMonitor'
go
use DiskMonitor
go
sp_adduser 'diskuser'
go
sp_addrolemember 'db_datawriter','Diskuser'
go
sp_addrolemember 'db_datareader','Diskuser'
go

Step3: Copy and paste the code below into c:\DiskSpacetoDB.vbs


'Objective: Find Disk Free Space in all the listed servers and write to a database
'Author: MAK
'Contact: mak_999@yahoo.com
Dim AdCn
Dim AdRec
Dim i, SQL
Set AdCn = CreateObject("ADODB.Connection")
Set AdRec = CreateObject("ADODB.Recordset")

' NOTE: Change the connection string according to your environment.
AdCn.Open = "Provider=SQLOLEDB.1;Data Source=
  SQL2K\instance1;Initial Catalog=DiskMonitor;user id = 'diskuser';password='disk' "
Set iFSO = CreateObject("Scripting.FilesyStemObject")
InputFile="c:\computerlist.txt"
Set ifile = iFSO.OpenTextFile(inputfile)  
Const MBCONVERSION= 1048576  
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
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() &"')"
wscript.echo sql
AdRec.Open SQL, AdCn,1,1
   end if
Next  
Loop

Step4: Execute c:\DiskSpacetoDB.vbs

When we execute this VB script, it will insert the values of servername, drive, disk space and free space into a SQL Server table.

SQL2k

C:

4000.527

1248.926

31.21903

12/27/03 1:40 AM

SQL2k

F:

5765.48

4271.188

74.08207

12/27/03 1:40 AM

SQL2k

L:

55003.77

54937.61

99.87973

12/27/03 1:40 AM

SQL2k

M:

59467.14

45513.91

76.53623

12/27/03 1:40 AM

YUKON

C:

18998.71

15492.5

81.54499

12/27/03 1:40 AM

YUKON

D:

19155.6

17229.18

89.94334

12/27/03 1:40 AM

DOTNET

C:

3999.977

1432.258

35.80666

12/27/03 1:41 AM

DOTNET

D:

5771.852

3308.375

57.31913

12/27/03 1:41 AM

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles