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

SQL Scripts & Samples

Posted Jul 7, 2008

DTS Package for Reporting Low Disk Space

By DatabaseJournal.com Staff


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



SQL Scripts & Samples Archives

Comment and Contribute

 


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

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date