'File:                  CheckSQLStorage.vbs
'Version:               1.0
'Author:                Bennett Scharf http://www.bennett-scharf.com
'Purpose:               Log SQL Server data file utilization
'                       Send alert when free space drops below a particular threshold
'Creation Date:         21 Mar 2007
'Revision Date: n/a
'Related Files:
'                       cscript.exe -vbscript console command intrepreter part of standard OS install
'                       databaseSize.log  csv output file
'Operating System:      Windows 2000 and later
'Usage:                 cscript LogDBSpace.vbs
'Exit codes:            Normal exit: 0,  Error Exit >1
'Note:                  Requires SQL-DMO components.  Refer to technet if you need to install on monitoring machine


Option Explicit

'Constants
Const strOutputFile= "D:\AutomationWorkFiles\DatabaseSize.log"
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const strDBServerName = "Some_Server_Name"
Const strDBName = "SomeDBName"
Const intDBMin = 1500, intTranMin = 2000'alerting thresholds size in MB
Const strMailSubject = "Database Storage Alert for DB SomeDBName"
Const strMailRecipient = "someName@someDomain.com" 'comma separate recipients
Const strMailFrom = "noreply@noDomain.com"
Const strMailTextBody = "DB free space low. Server: Some_Server_Name DB: SomeDBName."

'Global Variables
Dim objOutputFile
Dim strOutputPath
Dim strTime
Dim intDBSize, intDBSpaceLeft, intTranLogSize, intTranLogSpaceLeft
Dim objSQLServer, objDB, objTranLog, objDataFile


Set objSQLServer = CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure = True  'Using trusted connection / Windows authentication
objSQLServer.Connect strDBServerName

Set objDB = objSQLServer.Databases(strDBName)
Set objTranLog = objDB.TransactionLog
Set objDataFile = objDB.FileGroups("PRIMARY").DBFiles(1)

intDBSize =  objDataFile.size
intTranLogSize = objTranlog.Size
intDBSpaceLeft =  objDataFile.SpaceAvailableInMB
intTranLogSpaceLeft = CLng(objTranLog.SpaceAvailableInMB)

OpenOutputFile(strOutputFile)
objOutputFile.WriteLine Now & ", " & intDBSize & ", " & intDBSpaceLeft & ", " & intTranLogSize & ", " _
                            & intTranLogSpaceLeft
objOutputFile.Close
If intDBSpaceLeft < intDBMin Or intTranLogSpaceLeft < intTranMin Then
   SendMail strMailFrom, strMailRecipient, strMailSubject, strMailTextBody, strOutputFile
End If
wscript.Quit(0)

Sub OpenOutputFile(strOutputFile)
     Dim fso
     Set fso = CreateObject("Scripting.FileSystemObject")
     On Error Resume Next
     Set objOutputFile = fso.OpenTextFile(strOutputFile, ForAppending, True)
     If Err.Number <> 0 Then    'Trap file open errors and exit
         Wscript.echo Err.Description
         Wscript.quit (Err.Number)
     End If
    On Error GoTo 0
End Sub

Sub SendMail(strFrom, strRecipient, strSubject, strTextBody, strAttachment)
    Dim objEmail
    Set objEmail = CreateObject("CDO.Message")
    With objEmail
      .From = strFrom
      .To = strRecipient
      .Subject = strSubject
      .Textbody = strTextBody
      .AddAttachment strAttachment
      '.AddAttachment strOutputFile
      .Configuration.Fields.Item _
          ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
      .Configuration.Fields.Item _
          ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
              "10.190.4.11"
      .Configuration.Fields.Item _
          ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
      .Configuration.Fields.Update
      .Send
    End With
    Set objEmail = Nothing
End Sub