dcsimg
 

DTS Package for Reporting Low Disk Space

Monday Jul 7th 2008 by DatabaseJournal.com Staff

This VBscript code will check for low disk space on the local SQL Server. The script will check all disks except the C: drive and will trigger an alert to be sent via e-mail when the available disk space on any drive falls below 20%.


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

Home
Mobile Site | Full Site