Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 14, 2004

Monitor Disk Space on Multiple SQL Servers - Page 2

By DatabaseJournal.com Staff

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM