Monitor Disk Space on Multiple SQL Servers

Wednesday Jan 14th 2004 by DatabaseJournal.com Staff
Share:

Muthusamy Anantha Kumar discusses three methods to monitor disk space on a list of servers and store the output either in a .CSV file or on a database table.

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

by MAK [Muthusamy Anantha Kumar]

Method 3:

Check the drive space on all of the servers listed in a table and store the output such as Server name, Drive names, Disk Space and Free space 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 a table in SQL Server for storing and retrieving server names.


use DiskMonitor
go
Create Table Servers(ServerName varchar(128))
go
Insert into Servers select 'SQL2K'
Insert into Servers select 'YUKON'
Insert into Servers select 'DOTNET'
go

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

'Objective: Find Disk Free Space in all the listed servers 
'in a table and write to a database table
'Author: MAK
'Contact: mak_999@yahoo.com
Const MBCONVERSION= 1048576  
Dim AdCn
Dim AdRec
Dim i, SQL
Set AdCn = CreateObject("ADODB.Connection")
Set AdRec = CreateObject("ADODB.Recordset")
Set AdRec1 = 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' "
SQL1 = "Select ServerName from Servers"
'wscript.echo SQl1
AdRec1.Open SQL1, AdCn,1,1
'Adrec1.movefirst
while not Adrec1.EOF 
Computer	= Adrec1("ServerName")
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  
Adrec1.movenext
Wend

Step4: Execute c:\DiskSpacetoDBDB.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

Method 4

Check the drive space on all of the servers listed in a table and store the output such as Server name, Drive names, Disk Space and Free space on a SQL Server table. Also, store the error messages in a Log table.

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

Step1: Create a table in SQL Server for storing all the errors

Use DiskMonitor
Go
Create table logtable (id int identity(1,1), notes varchar(1000), date datetime default getdate())
go

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

'Create table logtable (id int identity(1,1), notes varchar(1000), date datetime default getdate())
'Objective: Find Disk Free Space in all the listed servers in a table and write to a database table
'Author: MAK
'Contact: mak_999@yahoo.com
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=SQL2k\instance1;
  Initial Catalog=DiskMonitor;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

Step3: Execute c:\DiskMonitor.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

In addition, Log information will be stored in a log table similar to the one below.

1

Disk Monitoring Started

1/11/2004

2

YUKON: The remote server machine does not exist or is unavailable

1/11/2004

3

DOTNET: The remote server machine does not exist or is unavailable

1/11/2004

4

Myserver: The remote server machine does not exist or is unavailable

1/11/2004

5

ETL: Access Denied

1/11/2004

6

Disk Monitoring - Completed

1/11/2004

You can also create a small .asp page (see attachment and rename the files from .txt to .asp) to display the status of the Disk Drives of all of the servers on a web page. The ASP page will look similar to those shown in default.txt and Log.txt.

The column "Percentage" in the .asp page will display in RED, YELLOW and GREEN if the percentage is less than 15% , between 15% to 20% and greater than 20% respectively.

The log table will appear in the asp page similar to the one below.

Conclusion:

By scheduling one of the above VB Scripts as a batch job, we can monitor the disk space on all of the servers on the network daily.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved