sp_dba_spacealert_dmo.vbs

'############################### sp_dba_spacealert_dmo.vbs ############################
' Submitted by Jim Mccoy  Divermack@yahoo.com
'This vbscript is used to alert and email receipients on Filegroup space usage
'Ouput:  '% Space Avail. , Space Avail. Filesize, MaxSize, FileName, DBname, FileGroup
'The process takes three arguments - SqlserverName , 
'Threshold for percentage of available space to check ,
'Mininum MaxFileSize to check
'Execute the script either from the command line or schedule the script via Sql Server Agent
'Command line execution:  cscript.exe sp_dba_spacealert_dmo.vbs sqlservername Threshold MaxfileSize
'Sql server agent : cscript sp_dba_spacealert_dmo.vbs  sqlservername Threshold MaxfileSize
'The process uses sqlmail which can be installed on any server within your organization
'######################################################################################
Dim oServer,oDatabase
Dim oDBgrp,oDBfgrp
Dim sResults,oDBfile 
Dim oDbfil,sDBfil
Dim SpAlert,Checksize

Dim sFgn,sDBfs

Dim oArgs, fso, drv
Dim command, recordset, connDb 
Nosend = False

'Constants used for the sendmail proc
Const sServer = "SSSSSSS"                  ' Name of server where Sqlmail is installed
Const sUser = "UUUUU"                      ' Sqlmail userid
Const sPassword = "PPPPPP"                 ' Sqlmail password 
Const sDatabase = "master"
Const messageto = "NNNNNN@company.com"     ' Mail recipient 
Const ccto = "NNNNNN@comapny.com"          ' cc Mail recipient 
' multiple addresses delimited by semicolon
Const width = "200"

 'Arguments for Sqlserver ,  Threshold  , MaxFileSizeCheck
 
 Sqlserver = WScript.Arguments.Item(0)
 Threshold = CInt(WScript.Arguments.Item(1))
 MaxCheck = CInt(WScript.Arguments.Item(2))
 
Set oServer = CreateObject("SQLDmo.SqlServer") 
 
oServer.LoginSecure = True 
 
oServer.Connect Sqlserver 
 
' This will return the name of each database along with the % space avail.
' space avail,  Filesize, MaxFileSize, FileName, DBName and FileGroup 

For Each oDatabase In oServer.Databases 
  Set oDBgrp = oDatabase
  Set oDBfgrp = oDBgrp.Filegroups
for each sDBfs in oDBfgrp
   Next
    for each oDBfil in oDBfgrp
     sFgn = oDBfil.name
        for each sDBfil in oDBfil.DBFiles 
SpAlert = Round((sDBfil.SpaceAvailableInMB/sDBfil.Size)* 100)
Checksize = Round(sDBfil.MaximumSize - sDBfil.Size)             ' Check to see if the filesize is approaching  maxfilesize
'  If you want to exclude primary filegroups
'If SpAlert < Threshold and  sDBfil.MaximumSize > -1 and sFgn <> "PRIMARY" and Checksize < MaxCheck then

'  If you want to include primary filegroups
If SpAlert < Threshold and  sDBfil.MaximumSize > -1  and Checksize < MaxCheck then

'Displays % space avail. , Space avail. Filesize, MaxSize, FileName, DBname, FileGroup
sTitle = "%SpAvl - SpAvl(mb) - Fsize - Msize - Fname - DB - FGN And MaxFsize < " & MaxCheck & "MB"
sResults = sResults & sTitle & Chr(13) & "    " & SpAlert & "%" &" - "& sDBfil.SpaceAvailableInMB & "MB" &" - "& sDBfil.Size & "MB" & " - " & sDBfil.MaximumSize & "MB" & " - " & sDBfil.Name & " - " & oDatabase.name & " - " & sFgn  &  Chr(13) 
  If IsEmpty(sResults) = False  then 
     Nosend = True
 End If
End If
    Next
Next
Next 



sConnect = "driver={SQL Server};server=SSSSSSS" + ";uid=" + _
sUser + ";pwd=" + sPassword + ";database=" + sDatabase
messagebody = sResults 

messagesubject = "Space Alert For: " & Sqlserver & " " & Now

Set command = WScript.CreateObject("ADODB.Command")
command.Activeconnection = sConnect



If Nosend = True Then' Don't send email if there's nothing to report on
command.CommandText = _
"EXEC master.dbo.xp_sendmail @recipients = '" + messageto + _
"',@copy_recipients = '" + ccto + _
"',@subject = '" + messagesubject + _
"',@message = '" + messagebody + _
"', @width = " + width
Set recordset = command.Execute()
End If

   
'Uncomment to display results in MsgBox  at terminal

'MsgBox  sResults 

  
oServer.DisConnect 
' Clean up
Set oServer = Nothing 
Set recordset = nothing
Set connDb  = nothing
Set command = nothing
Set recordset = nothing