Av rating:
Total votes: 10
Total comments: 3
Alert-based Transaction Log Backups - Automate your database Maintenance Part 2
20 April 2007
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):
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoEnum.dll
Microsoft.SqlServer.SqlEnum.dll
Or, with SQL Server Express…
Microsoft.SqlServer.Express.ConnectionInfo.dll
Microsoft.SqlServer.Express.Smo.dll
Microsoft.SqlServer.Express.SmoEnum.dll
Microsoft.SqlServer.Express.SqlEnum.dll
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 = "Me@MyOrganisation.com" ' who do we notify?
strNotificationSource = "MyServer@MyOrganisation.com" 'who notified?
strNotificationSMTP = "smtp.MyOrganisation.com" '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
Next
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" -Tmyname@myorg.com
...or ...
u:\DBMaint\ BuildAlertLogBackups -S"MyServer\Inst01" -Tmyname@myorg.com -G1
...if using the Redgate Backup
The job will connect to server "MyServer\Inst01", and notify mynam@myorg.com of the backup.
Try
'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
spStoredProc.Drop()
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))
spStoredProc.Parameters.Add(prmDBName)
'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
Else
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
spStoredProc.Create()
'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"))
jobDumpJob.Drop()
End If
Next
'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
Next
For Each objAlert In colAlerts
Dim altDropAlert As Alert
altDropAlert = srvMgmtServer.JobServer.Alerts(objAlert.Name)
altDropAlert.Drop()
Next
'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"
jobDumpJob.Create()
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
jbsDumpJobStep.Create()
intStepID = jbsDumpJobStep.ID
jobDumpJob.ApplyToTargetServer(srvMgmtServer.Name)
jobDumpJob.StartStepID = intStepID
jobDumpJob.Alter()
'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"
Else
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
altThresh.Create()
End If
Next
'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.
Console.WriteLine(smoex.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)
Console.WriteLine(ex.InnerException.Message)
ex = ex.InnerException
Loop
'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)
Console.WriteLine(ex.InnerException.Message)
ex = ex.InnerException
Loop
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
Else
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
This article has been viewed 8235 times.
Rate this article: Avg rating:
from a total of 10 votes.