Alert-based Transaction Log Backups – Automate your database Maintenance Part 2

Part 2 of Allen White's "SQL Server Automation" series demonstrates how to use 'performance condition' alerts for more effective database log backups during periods of high database activity.

Having a well thought-out, and tested, backup strategy is important to your disaster recovery plan. Your regular database backups and transaction log backups will allow you to recover from system failure or user error. However, one thing that even the best strategy cannot anticipate is that sudden flurry of transaction activity that fills up the transaction log rapidly. If you’ve disallowed ‘auto-grow’ on your transaction log you could find yourself with no space left and unhappy users. If, alternatively, you’ve allowed ‘auto-grow’, you may find that you’re taking performance hits as the growth occurs, and the available disk space on your server rapidly diminishes. However, there is a good way to avoid this problem.

SQL Server provides ‘performance condition’ alerts that can be used to trigger event-based jobs. I use the ‘Percent Log Full’ performance condition alert to start a job automatically. This job then backs up the transaction log on the database that originally caused the alert to fire. This means that a log backup takes place whenever the transaction log for that database exceeds 50% of its capacity. (make sure you do not accidentally set this sort of alert on a zero-length log!)

As I mentioned in the first part of this series, SMO (Server Management Objects) is an object library that you can use in .NET programs to manage SQL Server. These programs use SMO to examine any of your servers to determine what databases exist, and to create both the alerts and SQL Server Agent jobs that perform the maintenance that you require.

You will need Microsoft Visual Basic 2005. The Express edition works fine, and can be downloaded from Microsoft. You will need to create a new Console application.

The first thing you’ll need to do is to select Add Reference from the Project menu. Within the Add Reference dialog, use the Ctrl key to select the following assemblies (you may have to browse to the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder or C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE if you are using SQL Server Express):

Or, with SQL Server Express…

The source code for this application is included with this article. You can simply paste it into Module1.vb. Alternatively, you can paste in the following code…

In the code window at the top of the code, before any declarations, insert the following lines:

Imports Microsoft.SqlServer.Management.SMO
Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.SMO.Agent

Before any of the module code 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

You’ll also need to change the Sub Main() statement to:

Module Module1

This change will allow you to process the command-line arguments, which is the first thing we need to do in each application.

Most of my servers use named instances so I can’t just tell the program to connect to “.”. Therefore, I pass in the server name as the first parameter.

    Sub Main(ByVal CmdArgs() As String)
        Dim strServerName As String
        Dim i As Integer
        Dim intVersion As Integer
        Dim strBackupPassword As String
         ' the password for the Backup (Red Gate SQL Backup Only)
        Dim intRedgate As Integer
        Dim strNotificationEmail As String
        Dim strNotificationSource As String 'who notified?
        Dim strNotificationSMTP As String
        'What was the SMTP (not SQL 2000)?

        strServerName = "."      ' The name of the server we're connecting to
        strBackupPassword = ""   ' the password for the Backup (Red Gate SQL Backup Only)
        strNotificationEmail = "" ' who do we notify?
        strNotificationSource = "" 'who notified?
        strNotificationSMTP = "" 'What was the SMTP (not SQL 2000)?
        intRedgate = 0

        For i = 0 To UBound(CmdArgs)
            Dim strCmdArg As String
            strCmdArg = CmdArgs(i)
            If Left(strCmdArg, 1 ) = "-" Then
                Select Case Mid(strCmdArg, 2, 1)
                    Case "S"
                        strServerName = Mid(strCmdArg, 3, Len(strCmdArg) - 2)
                    Case "W"    'the backup password
                        strBackupPassword =  Mid(strCmdArg, 3, Len(strCmdArg) - 2)
                    Case "G"    'Are we doing a nice Redgate backup?
                        intRedgate = CInt(Mid(strCmdArg, 3, Len(strCmdArg) - 2))
                    Case "T"    'Email: who do we notify?
                        strNotificationEmail  = Mid(strCmdArg, 3, Len(strCmdArg) - 2)
                    Case "F"    'From Whom: who was the source?
                        strNotificationSource = Mid(strCmdArg, 3, Len(strCmdArg) - 2)
                    Case "E"    'what SMTP server?
                        strNotificationSMTP  = Mid(strCmdArg, 3, Len(strCmdArg) - 2)
                End Select
            End If

So, to use SQL Server’s native backup for the alert backups you could issue the following command line:

u:\DBMaint\ BuildAlertLogBackups -S”MyServer\Inst01″

…or …

u:\DBMaint\ BuildAlertLogBackups -S”MyServer\Inst01″ -G1

…if using the Redgate Backup

The job will connect to server “MyServer\Inst01”, and notify of the backup.

            'The first thing we'll do is connect to the server, and grab the
            'location of the backup directory from the Server.Settings object.

            Dim strBackupDir As String

            ' Connect to the server
            Dim srvMgmtServer As Server
            srvMgmtServer  = New Server(strServerName)
            Dim srvConn As ServerConnection
            srvConn = srvMgmtServer.ConnectionContext
            srvConn.LoginSecure = True
            srvMgmtServer.SetDefaultInitFields(GetType(Database), "IsSystemObject")

            'Determine the SQL Server Version
            intVersion = CInt(Left(srvMgmtServer.Information.Version.ToString, 1))

            'Build the Stored Procedure to perform the transaction log backups
            strBackupDir = srvMgmtServer.Settings.BackupDirectory

            'Next, we need to attach to a database and build the stored 
            'procedure framework. I've chosen to place the procedure into
            'the msdb database because 1) it's not the master database,
            'and 2) because I know it exists on every instance of SQL Server.
            'I haven't had any ill effects of this decision, but please leave
            'me comments if this would be considered outside "best practices".

            'Once I've connected to the database I create a StoredProcedure
            'object and name it 'db_log_dump'. I'm showing my Sybase roots
            'here, because that's what we called them pre-SQL Server 7. 
             'We also need to add a parameter to the stored procedure for
            'the name of the database to be backed up.

             Dim dbDatabase As Database
            Dim spStoredProc As StoredProcedure
            Dim prmDBName As StoredProcedureParameter   ' The database name passed
            Dim strSPText As String     ' The Stored Procedure command string

            dbDatabase = srvMgmtServer.Databases("msdb")
            spStoredProc = dbDatabase .StoredProcedures("db_log_dump")
            If Not (spStoredProc Is Nothing) Then
            End If
            spStoredProc = New StoredProcedure(dbDatabase, "db_log_dump")
            spStoredProc.TextMode = False
            spStoredProc.AnsiNullsStatus = False
            spStoredProc.QuotedIdentifierStatus = False
            prmDBName = New StoredProcedureParameter (spStoredProc, _
             "@database", DataType.VarChar(50))

            'Note that I check to see if the db_log_dump stored procedure
            'already exists, and drop it if it does, just to make sure we
            'don't run into an error.

            'Last, we build the text of the stored procedure. I've declared
            'two variables, one for the backup device name, and the other
            'for the string holding the date and time value to be part of
            'the log backup file name. The date value will hold the date
            'and time (to the second) that the backup was initiated. Once
            'the date string is built, then it's concatenated to the backup
            'directory, the database name and the '_tlog_' designator to let
            'me know it's a log backup. Tack the '.TRN' extension on and we've
            'got the full pathname of the backup file to pass to the BACKUP command.

            strSPText = "declare @strbackup varchar(500), @strDate varchar(30)" + vbCrLf
            strSPText = strSPText + _
             "set @strDate = CONVERT(varchar, getdate() , 112)" + vbCrLf
            strSPText =  strSPText + _
             "set @strDate = @strDate + Left(CONVERT(varchar, getdate() , 108),2)" _
             + vbCrLf
            strSPText = strSPText + _
             "set @strDate = @strDate + SubString(CONVERT(varchar,getdate(),108),4,2)" _
             + vbCrLf
            strSPText = strSPText + _
             "set @strDate = @strDate + SubString(CONVERT(varchar,getdate(),108),7,2)" _
             + vbCrLf
            If  intRedgate = False Then
                strSPText = strSPText + _
                 "set @strbackup = '" + strBackupDir _
                 + "' + '\' + @database + '_tlog_' + @strDate + '.TRN'" + vbCrLf
                strSPText = strSPText _
                + "BACKUP log @database to disk = @strbackup" + vbCrLf

                strSPText = strSPText +  "Declare @Command varchar(255) " + vbCrLf
                strSPText = strSPText + _
                 "set @strbackup = '" + strBackupDir _
                 + "' + '\' + @database + '_tlog_' + @strDate + '.sqb'" + vbCrLf
                strSPText = strSPText _
                + "BACKUP log @database to disk = @strbackup" + vbCrLf
                strSPText = strSPText _
                +  "Select @command= '-SQL ""BACKUP LOG @database TO DISK = @strBackup " _
                + "WITH " + IIf(strBackupPassword <> "", "PASSWORD = ''" _
                + strBackupPassword + "'',", "") + " "" -E ' " + vbCrLf
                strSPText = strSPText + "Execute master..sqlbackup @command"
            End If

            spStoredProc .TextBody = strSPText

            'Before we create the jobs, we need to clean up any existing jobs,
            'so we don't leave any orphans out there. Within SMO at different
            'levels are objects labeled "Enum", all of which return a DataTable
            'object, and they enumerate properties of the object. These are quite
            'useful in exploring your server, your database, or other objects in
            'SQL Server. In this case we're going to load a DataTable with the
            'EnumJobs object within the server's JobServer object. We'll then
            'loop through the rows returned in the DataTable and drop any jobs
            'whose name ends with "log_dump", because that's how we're naming
            'the transaction log backup jobs. (I know, Sybase again.)

            Dim tblServerJobs As DataTable  ' The existing jobs on the server
            Dim rowServerJobs As DataRow        ' A row for the server's jobs

            tblServerJobs = srvMgmtServer.JobServer.EnumJobs
            For Each rowServerJobs In tblServerJobs.Rows
                If Right(rowServerJobs("Name"), 8) = "log_dump" Then
                    Dim jobDumpJob As Job
                    jobDumpJob = srvMgmtServer.JobServer.Jobs(rowServerJobs("Name"))
                End If

            '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.

            '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 ' The collection of alerts on the server
            Dim altAlert As Alert
            Dim colAlerts As New Collection
             Dim objAlert As dbAlert

            'Delete existing jobs and 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
            For Each objAlert In colAlerts
                Dim altDropAlert As Alert
                altDropAlert = srvMgmtServer.JobServer.Alerts(objAlert.Name)

            'Now that the log_dump jobs and alerts have been successfully
            'dropped, we can loop through the databases, creating a new
            'log_dump job for each database we find that's not a system
            'database or a snapshot database, and creating the alert to
            'trigger the job. For each database we create a new Job with
            'the name of the database plus the "_log_dump" string so the
            'job is easily identified, define the remaining properties,
            'and create the job. We define a GUID variable called strJobID
            'to capture the internal ID of the job so we can use it in
            'defining the job step, next. 
            Dim dbcDatabases As DatabaseCollection

            ' Loop through the non-System databases to create the backup jobs
            ' and performance alerts
            dbcDatabases = srvMgmtServer.Databases
            For Each dbDatabase In dbcDatabases
                Dim bolProcessDB As Boolean

                bolProcessDB = True
                If dbDatabase.IsSystemObject = True _
              Or dbDatabase.DatabaseOptions.RecoveryModel = RecoveryModel.Simple Then
                    bolProcessDB = False
                 End If
                If dbDatabase.CompatibilityLevel = CompatibilityLevel.Version90 Then
                    If dbDatabase.IsDatabaseSnapshot Then
                        bolProcessDB = False
                    End If
                End If
                If bolProcessDB = True Then
                    Dim strDBName As String
                    Dim strPerfCond As String
                    Dim strJobID As Guid
                     Dim jobDumpJob As Job
                    Dim jbsDumpJobStep As JobStep
                    Dim jbsDumpFailStep As JobStep
                    Dim strDumpFailStep As String
                    Dim intStepID As Integer
                    Dim altThresh As Alert

                    strDBName = dbDatabase.Name

                    jobDumpJob =  New Job(srvMgmtServer.JobServer, strDBName + "_log_dump")
                    jobDumpJob.Description = "Threshold Backup for Database " + strDBName
                    jobDumpJob.Category = "[Uncategorized (Local)]"
                    jobDumpJob.OwnerLoginName = "sa"
                    strJobID = jobDumpJob.JobID

                    'The job step is defined to execute the db_log_dump stored
                    'procedure we created earlier, and we pass as a parameter
                    'the name of the database we're currently using, set the
                    'remaining properties and create the step. We then grab
                    'the StepID (integer) to set the Job.StartStepID to that
                    'value. We also need to set the Job.ApplyToTargetServer
                    'value to the name of the server we're running against,
                    'and then alter the job.

                    jbsDumpJobStep = New JobStep(jobDumpJob, "Step 01")
                     jbsDumpJobStep.DatabaseName = "msdb"
                    jbsDumpJobStep.Command = "exec db_log_dump '" + strDBName + "'"
                    jbsDumpJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
                    jbsDumpJobStep.OnFailAction = StepCompletionAction.GoToNextStep
                    intStepID = jbsDumpJobStep.ID

                     jobDumpJob.StartStepID = intStepID

                    '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.

                    If srvMgmtServer.InstanceName = "" Then
                        strPerfCond = "SQLServer"
                        strPerfCond = "MSSQL$" + srvMgmtServer.InstanceName
                    End If
                    strPerfCond = strPerfCond + _
                     ":Databases|Percent Log Used|" + strDBName + "|>|50"
                    altThresh = New Alert(srvMgmtServer.JobServer, strDBName + _
                     " _log_threshold")
                    altThresh.CategoryName = "[Uncategorized]"
                    altThresh.PerformanceCondition = strPerfCond
                    altThresh.JobID = strJobID
                End If

            'Finally we catch the exceptions and handle them.

        Catch smoex As SmoException
            Console.WriteLine("There has been an SMO error")
            ' Display the SMO exception message.
            'Display the sequence of non-SMO exceptions that caused the SMO exception.
            Dim ex As Exception
            ex = smoex.InnerException
            Do While ex.InnerException IsNot (Nothing)
                ex = ex.InnerException
            'Catch other non-SMO exceptions.
        Catch ex As Exception
            Console.WriteLine("There has been a VB error. " + ex.Message)
            Do While ex.InnerException IsNot (Nothing)
                ex = ex.InnerException
        End Try
    End Sub

    'We need to create a function for our failure reporting step. We want to use the 
    'same program whether we're running SQL Server 2000 or SQL Server 2005, so within our
    'BuildNotifyStep function we create the appropriate commands based on the version to
    'which we're connected. This function will return the Transact-SQL command which will
    'send the appropriate notification email, based on the step that failed. 
    'This function is used in all three programs, so it accepts the job name, server 
    'version and database name as parameters and uses them to construct a meaningful error
    'message. If the error is a server-level error an empty string is passed in the
    'database name parameter.

    Private Function BuildNotifyStep( _
     ByVal strJobName As String, _
     ByVal intVersion As Integer, _
     ByVal strDBName As String,  _
     ByVal strNotificationEmail As String, _
     ByVal strNotificationSource As String, _
     ByVal strNotificationSMTP As String) As String
        Dim strCmd As String

        If intVersion = 9 Then
            strCmd = "declare @rc int, @subj varchar(255), @mesg varchar(255)" + vbCrLf
            strCmd = strCmd + "select @subj = @@servername + ' - " + strJobName  _
            + " Job Failure'" + vbCrLf
            strCmd = strCmd + "select @mesg = 'The " + strJobName + " job"
            If strDBName <> "" Then
                strCmd = strCmd + " for database " + strDBName
            End If
            strCmd = strCmd + " on ' + @@servername + ' failed at ' + "
             strCmd = strCmd + "convert(varchar(25), getdate(), 100)" + vbCrLf
            strCmd = strCmd + "exec @rc = master.dbo.xp_smtp_sendmail" + vbCrLf
            strCmd = strCmd + " @FROM = N'" _
            + strNotificationSource + "'," + vbCrLf
            strCmd = strCmd + " @FROM_NAME = N'DB Admin'," + vbCrLf
            strCmd = strCmd + " @TO     = N'" + strNotificationEmail _
            + "'," + vbCrLf
            strCmd = strCmd + " @priority = N'NORMAL'," + vbCrLf
            strCmd = strCmd + " @subject = @subj," + vbCrLf
            strCmd = strCmd + " @message = @mesg," + vbCrLf
            strCmd = strCmd + " @type = N'text/plain'," + vbCrLf
            strCmd =  strCmd + " @server  = N'" + strNotificationSMTP + "'" + vbCrLf
            strCmd = "declare @rc int, @subj varchar(255), @mesg varchar(255)" + vbCrLf
            strCmd = strCmd + "select @subj = @@servername + ' - " + strJobName _
            + " Job Failure'" + vbCrLf
            strCmd = strCmd + "select @mesg = 'The " + strJobName +  " job"
            If strDBName <> "" Then
                strCmd = strCmd + " for database " + strDBName
            End If
            strCmd = strCmd + " on ' + @@servername + ' failed at ' + "
            strCmd = strCmd + "convert(varchar(25), getdate(), 100)" + vbCrLf
            strCmd = strCmd + "EXEC msdb.dbo.sp_send_dbmail"  + vbCrLf
            strCmd = strCmd + "  @profile_name = 'DBMail'," + vbCrLf
            strCmd = strCmd + "  @recipients = '" + strNotificationEmail + "'," + vbCrLf
            strCmd = strCmd + "  @body = @mesg," + vbCrLf
            strCmd = strCmd + "  @subject = @subj" + vbCrLf
        End If
        BuildNotifyStep  = strCmd

    End Function

End Module