In this second part of the post on building alert-based transaction log backups, I'm going to focus on creating the SQL Agent jobs that will call the stored procedure we created in Part One.
Before we create the jobs, though, 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
Dim rowServerJobs As DataRow
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"))
jobDumpJob.Drop()
End If
Next
Now that the log_dump jobs 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. 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.
The job step is defined to execute the db_log_dump stored procedure we created in Part One, 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, then alter the job. Here's the code:
Dim dbcDatabases As DatabaseCollection
Dim dbDatabase As Database
'Loop through the non-System databases to create the backup jobs and performance alerts
dbcDatabases = srvMgmtServer.Databases
For Each dbDatabase In dbcDatabases
If (Not dbDatabase.IsSystemObject) And (Not dbDatabase.IsDatabaseSnapshot) Then
Dim strDBName As String
Dim strJobID As Guid
Dim jobDumpJob As Job
Dim jbsDumpJobStep As JobStep
Dim intStepID As Integer
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"
jobDumpJob.Create()
strJobID = jobDumpJob.JobID
jbsDumpJobStep = New JobStep(jobDumpJob, "Step 1")
jbsDumpJobStep.DatabaseName = "msdb"
jbsDumpJobStep.Command = "exec db_log_dump '" + strDBName + "'"
jbsDumpJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
jbsDumpJobStep.OnFailAction = StepCompletionAction.QuitWithFailure
jbsDumpJobStep.Create()
intStepID = jbsDumpJobStep.ID
jobDumpJob.ApplyToTargetServer(srvMgmtServer.Name)
jobDumpJob.StartStepID = intStepID
jobDumpJob.Alter()
End If
Next
In the last post we'll add the code to set up the performance condition alert.
Allen