Cupfighter.net

' initialize SQL statement
SQL = "SET NOCOUNT ON; " & _
"DECLARE @dbid int DECLARE @dbname sysname DECLARE @sql varchar(3000) " & _
"CREATE TABLE #DBSpace ( " & _
" DBName varchar (50), " & _
" [Size] int, " & _
" max_size int, " & _
" growth int, " & _
" is_percentage_growth bit, " & _
" percentage_used real, " & _
" [type] varchar(15), " & _
" severity varchar(8)) " & _
"SET @dbid = (SELECT MIN(database_id) FROM master.sys.databases) " & _
"WHILE @dbid IS NOT NULL " & _
" BEGIN " & _
" SET @dbname = (SELECT [name] FROM master.sys.databases WHERE database_id = @dbid) " & _
" SET @sql = 'INSERT INTO #DBSpace SELECT dbname , [size], max_size, growth, is_percent_growth, percentage_used, " & _
" CASE " & _
" WHEN [type] = 0 THEN ''Database'' " & _
" WHEN [type] = 1 THEN ''Transaction Log'' " & _
" END AS [type], " & _
" CASE " & _
" WHEN is_percent_growth = 1 THEN " & _
" CASE " & _
" WHEN db.percentage_used < (100 - ((db.percentage_used / 100) * db.growth * 1.2)) AND db.percentage_used > (100 - ((db.percentage_used / 100) * db.growth * 2.5)) THEN ''warning'' " & _
" WHEN db.percentage_used > (100 - ((db.percentage_used / 100) * db.growth * 1.2)) THEN ''critical'' " & _
" ELSE ''healthy'' " & _
" END " & _
" ELSE " & _
" CASE " & _
" WHEN db.size < (db.max_size - (db.growth * 2)) AND db.size > (db.max_size - (db.growth * 4)) THEN ''warning'' " & _
" WHEN db.size > (db.max_size - (db.growth * 2)) THEN ''critical'' " & _
" ELSE ''healthy'' " & _
" END " & _
" END AS severity " & _
" FROM " & _
" ( SELECT ''' + @dbname + ''' as dbname, [size] , [type], [max_size], [growth] , " & _
" [is_percent_growth] , " & _
" (CAST([size] AS REAL) / CAST([max_size] AS REAL) * 100) AS percentage_used " & _
" FROM [' + @dbname + '].sys.database_files )AS db " & _
" WHERE max_size > 0 AND growth > 0 ' " & _
" EXEC(@sql) " & _
" SET @dbid = (SELECT MIN(database_id) FROM master.sys.databases WHERE database_id > @dbid) " & _
" END " & _
"SELECT * FROM #DBSpace WHERE severity <> 'healthy' " & _
"drop table #DBSpace"

On Error resume next
' Setup Database connection
Set cnADOConnection = CreateObject("ADODB.Connection")
cnADOConnection.Provider = "sqloledb"
cnADOConnection.ConnectionTimeout = 30
strProv = "Server=" & sConnectionString & ";Database=Master;Trusted_Connection=yes"
' open connection and execute query
cnADOConnection.Open strProv
Set rsCustomDBSpaceMonitor = cnADOConnection.Execute(SQL)
do until rsCustomDBSpaceMonitor.EOF
strDatabase = CStr(rsCustomDBSpaceMonitor("DBName").Value)
if rsCustomDBSpaceMonitor("severity").Value = "warning" Then
strSeverityWarning = "Warning"
strList = strList & "warning: " & rsCustomDBSpaceMonitor("type").Value & " " & strDatabase & " Percentage Used: " & rsCustomDBSpaceMonitor("percentage_used").Value & ", "
elseif rsCustomDBSpaceMonitor("severity").Value = "critical" Then
strSeverityCritical = "Critical"
strList = strList & "Critical: " & rsCustomDBSpaceMonitor("type").Value & " " & strDatabase & " Percentage Used: " & rsCustomDBSpaceMonitor("percentage_used").Value & ", "
else
strSeverityHealthy = "Healthy"
End If
' wscript.echo "percentage used " & rsCustomDBSpaceMonitor("percentage_used").Value

Err.Clear
rsCustomDBSpaceMonitor.MoveNext
' if 0 <> Err.number then Exit Do
loop
' clean up connection
rsCustomDBSpaceMonitor.close
cnADOConnection.close

' Set SCOM parameters
Set oAPI = CreateObject("MOM.ScriptAPI")
' set state depending on Free space (order of next statemenst is important, if only in one row the state is critical, the state will be critical)
If strSeverityHealthy = "Healthy" Then strStatus = "Healthy"
If strSeverityWarning = "Warning" Then strStatus = "Warning"
If strSeverityCritical = "Critical" Then strStatus = "Critical"

If strStatus = "Critical" Then
' wscript.echo strList
Call oAPI.LogScriptEvent("SBPDBFreeSpaceMonitor", 3000, EVENT_TYPE_ERROR, "The Following datafiles are low on free space:" & strList)