I would like to thank smurfhell for his unbeknownst help on getting this script working. I had to reference one of his posts to get this working. Anyway, this particular script searches for ldf files for an SQL database and if it greater than 2GB, it send me an e-mail. You can easily change this to any extension by modifying

CODE

If lcase(Right(objFile.Name,3)) = "ldf" Then

to have any extension. You can also modify the following line for other file sizes.

CODE

round(objFile.Size/1073741824,1)

KB = round(objFile.Size/1024,1)
MB = round(objFile.Size/1048576,1)
GB = round(objFile.Size/1073741824,1)

' // **************************************
' //    ComputerHighGuys recursive search
' //
' //    Date Created: 20 Aug 07
' //
' // Modify the SMTP Server for your SMTP
' //  server. Also change the notifyEmail
' //  address to whatever e-mail addresses
' //  you want to be notified.  Set the
' //  debug to False when you put it into
' //  production.
' // **************************************
SMTPServer = "mail.domain.com"
notifyEmail = "User1@domain.com;user2@domain.com"
setDebug = "True"

' // Directory where the SQL Server is installed
sqlDir = "D:\Program Files\Microsoft SQL Server\"
fileList = ""
set objFSO=CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(sqlDir)
Set colFiles = objFolder.Files

ScanSubFolders(objFolder)

if fileList <> "" then
    If setDebug = "True" then
        wscript.echo fileList
    End If
    Call SendErrorMessage(notifyEmail,fileList)
End if

Sub scanSubFolders(objFolder)

    Set colFolders = objFolder.SubFolders

    For Each objSubFolder In colFolders
        Set colFiles = objSubFolder.Files

        For Each objFile in colFiles
            ' // Looking for SQL Database LDF files.

            If lcase(Right(objFile.Name,3)) = "ldf" Then

                ' // Getting File size in GB
                If round(objFile.Size/1073741824,1) > 2 then
                    If setDebug = "True" then
                        wscript.echo objFile.Name & "  " & round(objFile.Size/1073741824,1)  & "GB"
                    End if
                    fileList = objFile.Name & "  " & round(objFile.Size/1073741824,1)  & "GB" & vbCRLF & vbCRLF & fileList
                End if

            End If
        Next
        ScanSubFolders(objSubFolder)
    Next

End Sub

Sub SendErrorMessage (strDestEmail,errorMsg)

    Set objEmail = CreateObject("CDO.Message")
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objShell = WScript.CreateObject ("WScript.Shell")

    If IsNull(strDestEmail) Then
        If debugMode = "True" then
            Wscript.Echo "No email address, no message sent."
        End If
        Exit Sub
    End If

    objEmail.From = "Server@domain.com"
    objEmail.To = strDestEmail
    objEmail.Subject = "Database log files that are greater than 2 GB"
    objEmail.Textbody = errorMsg

    objEmail.Configuration.Fields.Item _
        ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    objEmail.Configuration.Fields.Item _
        ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
    objEmail.Configuration.Fields.Item _
        ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    objEmail.Configuration.Fields.Update
    objEmail.Send

End Sub

Back to VBScript FAQ Index
Back to VBScript Forum
My FAQ Archive

Email This FAQ To A Friend

My Archive

My FAQ Archive