Today I decided to share a script I wrote that checks for a specific database log size and if it exceeds a certain predefined limit sends an alert email to a user of choice.

Of course, there is a possibility to add more databases to check and/or more database files.

Thought the script might come up handy for a DBA trying to avoid a situation with a rapidly expanding log file thus bringing down its database by scheduling it to run in short intervals, here is the code:

   1:  Dim Connection
   2:  Dim ConnString
   3:  Dim Recordset
   4:  Dim SQL
   5:  Dim Threshold
   6:  Dim CurrentLevel
   7:   
   8:  Thershold = <YOUR VALUE>  ' E.g. 100.00
   9:   
  10:  ' connection string
  11:  ConnString="DRIVER={SQL Server};SERVER=<YOUR SERVER>;UID=<YOUR USER ID>;PWD=<YOUR PASSWORD>;DATABASE=<YOUR DATABASE>"
  12:   
  13:  ' to check db log file size 
  14:  SQL = "DBCC SQLPERF(logspace)"
  15:   
  16:  Set Connection = CreateObject("ADODB.Connection")
  17:  Set Recordset = CreateObject("ADODB.Recordset")
  18:   
  19:  Connection.Open ConnString
  20:  Recordset.Open SQL,Connection
  21:   
  22:  ' determine if there are records 
  23:  If Recordset.Eof Then 
  24:      MsgBox("No records returned.") 
  25:  Else 
  26:      Do While NOT Recordset.Eof           
  27:          If Recordset("DATABASE NAME") = "<YOUR DATABASE>" Then 
  28:              MsgBox Recordset("DATABASE NAME") & " " & Recordset("Log Size (MB)")
  29:              CurrentLevel = Recordset("Log Size (MB)")
  30:              Exit Do
  31:          End If
  32:      Recordset.MoveNext     
  33:      Loop
  34:  End If
  35:   
  36:  If CurrentLevel > Thershold Then 
  37:      ' sending alert...
  38:      SendMail "<TARGET EMAIL>", "The log file of <YOUR DATABASE> has exceeded its threshold!" 
  39:  End If
  40:   
  41:  'close the connection and recordset objects to free up resources
  42:  Recordset.Close
  43:  Set Recordset=nothing
  44:  Connection.Close
  45:  Set Connection=nothing
  46:   
  47:  ' procedure to send an email using CDO
  48:  Sub SendMail(vTo,vSubject)
  49:      Dim Flds 
  50:      Dim strHTML
  51:   
  52:      Const cdoSendUsingPort = 2
  53:   
  54:      set iMsg = CreateObject("CDO.Message")
  55:      set iConf = CreateObject("CDO.Configuration")
  56:   
  57:      Set Flds = iConf.Fields
  58:   
  59:      With Flds
  60:          .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
  61:          .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "<YOUR SMTP EMAIL SERVICE PROVIDER>" 
  62:          .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10  
  63:          .Update
  64:      End With
  65:      
  66:      strHTML = "<HTML>"
  67:      strHTML = strHTML & "<HEAD>"
  68:      strHTML = strHTML & "<BODY>"
  69:      strHTML = strHTML & "Please take action:"
  70:      strHTML = strHTML & "<br>"
  71:      strHTML = strHTML & "The log file of <YOUR DATABASE> has exceeded its threshold!"
  72:      strHTML = strHTML & "</BODY>"
  73:      strHTML = strHTML & "</HTML>"
  74:   
  75:      With iMsg
  76:          Set .Configuration = iConf
  77:          .To = vTo 
  78:          .From = "<YOUR FROM EMAIL ACCOUNT>" 'E.g. noreply@dummy.com
  79:          .Subject = vSubject
  80:          .HTMLBody = strHTML
  81:          .Send
  82:      End With
  83:   
  84:      Set iMsg = Nothing
  85:      Set iConf = Nothing
  86:      Set Flds = Nothing
  87:   
  88:  End Sub