Allen White

Building Alert-based Transaction Log Backups - Part Three

In this final installment on building alert-based transaction log backups we'll build the alerts that will kick off the backup jobs.

The first thing we need to do is define a class to hold a collection of object names. We need to drop the existing alerts before creating new ones, but if you try to drop an object from within a collection of those objects .Net will throw an error. There's no "enum" function to give us the alerts defined on the server, but we can collect the names of the qualifying alerts from the Alerts collection. Then we can loop through our collection and drop the existing alert objects.

Public Class dbAlert
    Public name As String
    Sub New(ByVal newName As String)
        name = newName
    End Sub
End Class

Then, within the Main routine we can drop the alerts. I've used the word "threshold" at the end of the alert name (yep, Sybase again) to identify the Performance Condition alerts to watch the transaction log "threshold".

        Dim colAlertColl As AlertCollection
        Dim altAlert As Alert
        Dim colAlerts As New Collection
        Dim objAlert As dbAlert

        'Delete existing alerts
        colAlertColl = srvMgmtServer.JobServer.Alerts
        For Each altAlert In colAlertColl
            If Right(altAlert.Name, 9) = "threshold" Then
                If Not (colAlerts.Contains(altAlert.Name)) Then
                    colAlerts.Add(New dbAlert(altAlert.Name), altAlert.Name)
                End If
            End If
        Next
        For Each objAlert In colAlerts
            Dim altDropAlert As Alert
            altDropAlert = srvMgmtServer.JobServer.Alerts(objAlert.name)
            altDropAlert.Drop()
        Next

The last thing we need to do is create the alert. We need to know if we're working with a default or named instance, and we can get that from the InstanceName property of the Server object. If it's empty, then the Performance Condition starts with "SQLServer", otherwise it starts with "MSSQL$" followed by the name of the instance. Add to that the actual performance condition to be monitored, in this case ":Databases|Percent Log Used", plus the database name, then the value to be watched "|>|50". We then create a new Alert object, set the name to be the name of the database concatenated with "_log_threshold", the CategoryName to "[Uncategorized]", the PerformanceConditon to the string we built, and set the job to be initiated to the JobID we created in Part Two. (Note that this code goes right after the "jobDumpJob.Alter()" code from Part Two.) Call the Create method and we're done.

                Dim altThresh As Alert

                'Define the Performance Condition for the Alert
                If srvMgmtServer.InstanceName = "" Then
                    strPerfCond = "SQLServer"
                Else
                    strPerfCond = "MSSQL$" + srvMgmtServer.InstanceName
                End If
                strPerfCond = strPerfCond + "Databases|Percent Log Used|" + strDBName + "|>|50"

                'Define the Alert	
                altThresh = New Alert(srvMgmtServer.JobServer, strDBName + "_log_threshold")
                altThresh.CategoryName = "[Uncategorized]"
                altThresh.PerformanceCondition = strPerfCond
                altThresh.JobID = strJobID
                altThresh.Create()

Once you run this code you'll have automatically triggered transaction log backups whenever the log exceeds 50 percent of its capacity, which means you're less likely to run out of space in your transaction log, which of course means less down time, and that's a good thing, right?

Allen