DTS Package for Reporting Low Disk Space

July 7, 2008


This VBscript code will check for low disk space on the local SQL Server. Schedule the package to run once a day, and let the job report to you by e-mail when a drive runs low on free space.

The script will check all disks except for the C:\ drive and will trigger an alert to be sent when the available disk space on any drive falls below 20% remaining. The code uses the WMI object library which comes with Windows 2000 and XP. WMI can also be installed on NT.

Author: Lennart Gerdvall


Function Main()
	REM WHAT IT DOES FOR YOU:
	REM This code will check for low disk space on the local SQL Server.
	REM Schedule the package to run once a day and let the job report to
	REM you by e-mail when it fails. The code below checks all disks but
	REM the C-disk and fails when disk space is below 20% remaining.
	
	REM HOW TO INSTALL IT:
	REM Create a DTS package with the VB cript code below.
	REM Set up a connection to the local server with Windows Authenication.
	REM The Windows user running the package must be admin on the machine being checked.
	
	REM OTHER REQUIREMENTS:
	REM The package requires Windows Management Instrumentation (WMI), which is
	REM included in Windows 2000 and later. If you have Windows 95/98/NT 4.0, install
	REM the WMI core library (wmicore.exe).
	REM More information - http://msdn.microsoft.com/downloads/sdks/wmi/default.asp 

	DIM DiskSet
	DIM myDisk, test
	
	myDisk = ""
	test = 0
	
  Set DiskSet = GetObject("winmgmts:{impersonationLevel=Delegate}").ExecQuery("select FreeSpace,Size,Name from Win32_LogicalDisk where DriveType=3")
	
	For Each Disk In DiskSet
		if disk.name <> "C:" then
			test = (Disk.FreeSpace / Disk.Size)
			If test <= 0.20 then
				myDisk = myDisk + Disk.Name
			End If
		end if
	Next
	
	If myDisk <> "" Then 
		REM Yes, there are disks with LOW SPACE
	             Main = DTSTaskExecResult_Failure
	Else			 
		REM No, there no are disks with LOW SPACE
		Main = DTSTaskExecResult_Success
	End If
End Function


Back to Database Journal Home








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers