Av rating:
Total votes: 9
Total comments: 4
Automate your Database Integrity checks using SMO
15 September 2007
Automate your Database Maintenance using SMO - Part 3
Allen White shows how to use Visual Basic to create simple routines that use SMO for inserting and deleting jobs from SQL Server Agent and for creating or deleting stored procedures. He then shows how to use these routines to automatically check the integrity of all the appropriate databases in any server, and optimize them.This is the third article in the series. See also ...
Automate your Database backups using SMO and
Alert-based Transaction Log Backups
(The source code to this article can be accessed via the speech-bubble above)
Once you've implemented a well thought-out backup plan you'll want to perform regular optimization processes and check the integrity of your databases. These steps will help to keep the performance of your database activity at its peak. The integrity checks use DBCC CHECKDB commands to ensure no problems exist in your databases. Books Online has an excellent discussion of the DBCC CHECKDB command. Optimizations are done in this process by performing an UPDATE STATISTICS command against all database tables and an ALTER INDEX REORGANIZE command against all indexes. If there are other steps you normally do during regular maintenance feel free to include those steps in your implementation of this code.
As I mentioned in Part 1 of this series, SMO (Server Management Objects) is an object library which allows you to write programs to manage SQL Server. These programs use SMO to both examine the server on which it runs to determine what databases exist and to create the alerts and SQL Server Agent jobs which perform the maintenance required.
Creating and running the VB Script
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):
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoEnum.dll
Microsoft.SqlServer.SqlEnum.dll
Next, in the code window at the top of the code, before any declarations, insert the following lines:
Microsoft.SqlServer.Management.SMO
Imports Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.SMO.Agent
Module Module1
You'll also need to change the Sub Main() statement to:
Sub Main(ByVal CmdArgs() As String)
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.
Dim strServerName As String
Dim i As Integer
Dim intVersion 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
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)?
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 "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 rebuild the maintenance jobs you could issue the following command line (I run the jobs weekly, hence the name, but you can determine your own schedule):
u:\DBMaint\ BuildWeeklyMaintJobs -S"MyServer\Inst01" -Tmyname@myorg.com
The job will connect to server "MyServer\Inst01", and notify mynam@myorg.com of the backup.
The first thing we want to do is to connect to our server, set initial default fields to improve SMO performance, and determine the version of SQL Server we’re connecting to.
Try
' 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))
' Set the initial default fields to improve SMO performance
srvMgmtServer.SetDefaultInitFields(GetType(Table), "Name")
srvMgmtServer.SetDefaultInitFields(GetType(View), "Name")
srvMgmtServer.SetDefaultInitFields(GetType(Index), "Name")
srvMgmtServer.SetDefaultInitFields(GetType(Table), "IsSystemObject")
srvMgmtServer.SetDefaultInitFields(GetType(View) , "IsSystemObject")
The next step is to drop the existing maintenance jobs. I create separate jobs for system databases and user databases for optimizations, and one job for checking database integrity. The four job names I use are ‘CheckDBIntegrity’, ‘SystemDBOptimizations’ and ‘UserDBOptimizations’. These commands drop them so the rest of the program and build new copies.
'Drop the existing optimization and integrity jobs job
tblServerJobs = srvvMgmtServer.JobServer.EnumJobs
For Each rowServerJobs In tblServerJobs.Rows
If rowServerJobs("Name") = "CheckDBIntegrity" _
Or rowServerJobs("Name") = "SystemDBOptimizations" _
Or rowServerJobs("Name") = "UserDBOptimizations" Then
jobDumpJob = srvMgmtServer.JobServer.Jobs(rowServerJobs("Name"))
jobDumpJob.Drop()
End If
Next
Now we’re ready to perform the subroutines that build the three jobs:
BuildDBIntegrity (srvMgmtServer) ' Build CheckDBIntegrity job
BuildSystemDBOpt(srvMgmtServer) ' Build SystemDBOptimizations job
BuildUserDBOpt(srvMgmtServer) ' Build UserDBOptimizations job
This is the end of the main subroutine, so we want to catch any errors that might have occurred, using the following code:
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 also need the BuildNotifyStep module we used in parts 1 and 2. It can be included here or at the end of the module.
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
' This function was described in Part 1
End Function
Creating a job to Check Database Integrity
The first job we’ll create is the one which checks the database integrity of all databases on our server, except tempdb. (If you’re concerned about the integrity of tempdb, restart your server. It’ll be refreshed from model and you’ll have a good tempdb again.) We use the DBCC CHECKDB WITH NO_INFOMSGS command to verify the integrity of each database on the server. First we’ll declare our subroutine and the variables it requires.
Sub BuildDBIntegrity(ByRef srvMgmtServer As Server)
Dim strJobID As Guid ' The JobID assigned to the job we're creating
Dim intStepID As Integer ' The StepID for each step created in the job
Dim dbcDatabases As DatabaseCollection ' The database collection on the target server
Dim dbDatabase As Database ' The Database object for the current database
Dim intVersion As Integer ' The SQL Server version number
Dim jobDBIntJob As Job ' The Job object for the created job
Dim jbsDBIntJobStep As JobStep ' The JobStep object for the current step
Dim jbsDBIntFailStep As JobStep ' The JobStep object for the failure step
Dim jbschDBIntJobSched As JobSchedule ' The JobSchedule object for the schedule
Dim strDBIntFailCmd As String ' The string with the command to execute on failure
Dim i As Integer ' An iterator
Try
Next, we’ll get the database collection on the server, set the default initial fields for more efficient SMO operations, and create the Agent job to check the database integrity.
' Get the database collection from the server and set the default init fields
dbcDatabases = srvMgmtServer.Databases
srvMgmtServer.SetDefaultInitFields(GetType(Table), "Name")
srvMgmtServer.SetDefaultInitFields(GetType(View), "Name" )
srvMgmtServer.SetDefaultInitFields(GetType(Index), "Name")
srvMgmtServer.SetDefaultInitFields(GetType(Table), "IsSystemObject")
srvMgmtServer.SetDefaultInitFields(GetType(View), "IsSystemObject")
'Determine the SQL Server Version
intVersion = CInt(Left(srvMgmtServer.Information.Version.ToString, 1))
'Create the Check DB Integrity Job
jobDBIntJob = New Job(srvMgmtServer.JobServer, "CheckDBIntegrity")
jobDBIntJob.Description = "Check DB Integrity"
jobDBIntJob.Category = "[Uncategorized (Local)]"
jobDBIntJob.OwnerLoginName = "sa"
jobDBIntJob.Create()
strJobID = jobDBIntJob.JobID
i = 0
We have to step through each database object in the collection, and for each database (except tempdb) create a step to run the DBCC command against the database. We’ll also set the starting step for the job.
' We need a checkdb step and a failure step for each database except tempdb
For Each dbDatabase In dbcDatabases
If dbDatabase.Name <> "tempdb" Then
i += 1
jbsDBIntJobStep = New JobStep(jobDBIntJob, "Step " + CStr(i))
jbsDBIntJobStep.DatabaseName = dbDatabase.Name
jbsDBIntJobStep.Command = "DBCC CHECKDB WITH NO_INFOMSGS"
jbsDBIntJobStep.OnSuccessAction = StepCompletionAction.GoToStep
jbsDBIntJobStep.OnSuccessStep = i + 2
jbsDBIntJobStep.OnFailAction = StepCompletionAction.GoToNextStep
jbsDBIntJobStep.Create()
intStepID = jbsDBIntJobStep.ID
If i = 1 Then
jobDBIntJob.ApplyToTargetServer(srvMgmtServer.Name)
jobDBIntJob.StartStepID = intStepID
jobDBIntJob.Alter()
End If
We now build the step to notify us if the DBCC fails, and add a dummy step for the last database’s DBCC step to go to when it succeeds.
strDBIntFailCmd = BuildNotifyStep("CheckDBIntegrity", intVersion, _ dbDatabase.Name)
i += 1
jbsDBIntFailStep = New JobStep(jobDBIntJob, "Step " + CStr(i))
jbsDBIntFailStep.DatabaseName = "master"
jbsDBIntFailStep.Command = strDBIntFailCmd
jbsDBIntFailStep.OnSuccessAction = StepCompletionAction.GoToNextStep
jbsDBIntFailStep.OnFailAction = StepCompletionAction.GoToNextStep
jbsDBIntFailStep.Create()
End If
Next
' Here we add a "dummy" step for the last checkdb step to go to on success
i += 1
jbsDBIntJobStep = New JobStep(jobDBIntJob, "Step " + CStr(i))
jbsDBIntJobStep.DatabaseName = "master"
jbsDBIntJobStep.Command = "select 1"
jbsDBIntJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
jbsDBIntJobStep.OnFailAction = StepCompletionAction.QuitWithSuccess
jbsDBIntJobStep.Create()
Then we create the schedule object and set the properties to occur once a week, on Sunday at midnight, starting today.
'Define a JobSchedule object variable by supplying the parent job
'and name arguments in the constructor.
jbschDBIntJobSched = New JobSchedule(jobDBIntJob, "Sched 01")
'Set properties to define the schedule frequency, and duration.
jbschDBIntJobSched.FrequencyTypes = FrequencyTypes.Weekly
jbschDBIntJobSched.FrequencyRecurrenceFactor = 1
jbschDBIntJobSched.FrequencySubDayTypes = FrequencySubDayTypes.Once
Dim tsDBIntStart As TimeSpan
tsDBIntStart = New TimeSpan(0, 0, 0)
jbschDBIntJobSched.ActiveStartTimeOfDay = tsDBIntStart
Dim tsDBIntEnd As TimeSpan
tsDBIntEnd = New TimeSpan(23, 59, 59)
jbschDBIntJobSched.ActiveEndTimeOfDay = tsDBIntEnd
jbschDBIntJobSched.FrequencyInterval = 1
Dim dtDBIntStartDate As Date = Date.Today
jbschDBIntJobSched.ActiveStartDate = dtDBIntStartDate
'Create the job schedule on the instance of SQL Agent.
jbschDBIntJobSched.Create()
The Catch block notifies us if any problems in the subroutine, and uses the same code as the Catch block in the main routine, and we’re done with the Check Integrity job.
End Sub
System Database Optimizations
In each system database we want to shrink the database, leaving 10 percent free space, for each table within the database we want to update statistics, and for each view we want to update statistics with the FULLSCAN and NORECOMPUTE options. Your optimizations can include the tasks you find most useful.
Sub BuildSystemDBOpt(ByRef srvMgmtServer As Server)
Dim strJobID As Guid ' The JobID assigned to the job we're creating
Dim intStepID As Integer ' The StepID for each step created in the job
Dim dbcDatabases As DatabaseCollection ' The database collection on the target server
Dim dbDatabase As Database ' The Database object for the current database
Dim intVersion As Integer ' The SQL Server version number
Dim jobSysDBOptJob As Job ' The Job object for the created job
Dim jbsSysDBOptJobStep As JobStep ' The JobStep object for the current step
Dim jbsSysDBOptFailStep As JobStep ' The JobStep object for the failure step
Dim jbschSysDBOptJobSched As JobSchedule ' The JobSchedule object for the schedule
Dim strSysDBOptFailCmd As String ' The string with the command to execute on failure
Dim i As Integer ' An iterator
Try
Next, we’ll get the database collection on the server, set the default initial fields for more efficient SMO operations, and create the Agent job.
' Get the database collection from the server and set the default init fields
dbcDatabases = srvMgmtServer.Databases
srvMgmtServer.SetDefaultInitFields(GetType(Table), "Name")
srvMgmtServer.SetDefaultInitFields(GetType(View), "Name")
srvMgmtServer.SetDefaultInitFields(GetType(Index), "Name")
srvMgmtServer.SetDefaultInitFields(GetType( Table), "IsSystemObject")
srvMgmtServer.SetDefaultInitFields(GetType(View), "IsSystemObject")
'Determine the SQL Server Version
intVersion = CInt(Left(srvMgmtServer.Information.Version.ToString, 1))
'Create the SystemDB Optimizations Job
jobSysDBOptJob = New Job(srvMgmtServer.JobServer, "SystemDBOptimizations")
jobSysDBOptJob.Description = "SystemDB Optimizations"
jobSysDBOptJob.Category = "[Uncategorized (Local)]"
jobSysDBOptJob.OwnerLoginName = "sa"
jobSysDBOptJob.Create()
strJobID = jobSysDBOptJob.JobID
i = 0
SQL Server 2000 provided the system stored procedure xp_sqlmaint which performed these optimization steps for us (after setting the options in the Database Maintenance Plan Wizard. These same actions are performed in SQL Server 2005 via the DBCC SHRINKDATABASE and UPDATE STATISTICS commands.
' In each database we want to shrink the database, leaving 10 percent free space,
' for each table within the database we want to update statistics, and for each view
' we want to update statistics with the FULLSCAN and NORECOMPUTE options.
For Each dbDatabase In dbcDatabases
If dbDatabase.IsSystemObject = True Then
If dbDatabase.Name <> "tempdb" Then
Dim strOptCmd As String ' The optimizations command string
Dim objTableColl As TableCollection ' The collection of tables
Dim objTable As Table ' A table object for iteration
Dim objViewColl As ViewCollection ' The collection of views
Dim objView As View ' A view object for iteration
i += 1
If intVersion = 9 Then
' Shrink Database Command
strOptCmd = "DBCC SHRINKDATABASE(N'" + dbDatabase.Name + "', _
10, TRUNCATEONLY)" + vbCrLf
strOptCmd = strOptCmd + "" + vbCrLf
objTableColl = dbDatabase.Tables ' Get the database tables
objViewColl = dbDatabase.Views ' Get the database views
' Update Statistics
For Each objTable In objTableColl
If objTable.IsSystemObject = False Then
strOptCmd = strOptCmd + "UPDATE STATISTICS [" + _
objTable.Schema + "].[" + objTable.Name + "]" + vbCrLf
End If
Next
For Each objView In objViewColl
If objView.IsSystemObject = False Then
strOptCmd = strOptCmd + "UPDATE STATISTICS [" + _
objView.Schema + "].[" + objView.Name + "] _
WITH FULLSCAN ,NORECOMPUTE" + vbCrLf
End If
Next
strOptCmd = strOptCmd + "" + vbCrLf
Else
' xp_sqlmaint handles this maintenance in SQL Server 2000 servers
strOptCmd = "EXECUTE master.dbo.xp_sqlmaint N'-D " + _
dbDatabase.Name + _
" -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '" + vbCrLf
End If
' Add the Job Step, and assign the command just built to the step
jbsSysDBOptJobStep = New JobStep(jobSysDBOptJob, "Step " + CStr(i))
jbsSysDBOptJobStep.DatabaseName = dbDatabase.Name
jbsSysDBOptJobStep.Command = strOptCmd
jbsSysDBOptJobStep.OnSuccessAction = StepCompletionAction.GoToStep
jbsSysDBOptJobStep.OnSuccessStep = i + 2
jbsSysDBOptJobStep.OnFailAction = StepCompletionAction.GoToNextStep
jbsSysDBOptJobStep.Create()
intStepID = jbsSysDBOptJobStep.ID
We need to assign the first job step to the job, and build the failure notification step for each step in the job, and add a dummy step for the last step to go to when it succeeds.
.
If i = 1 Then ' If this is the first job step, assign it to the job
jobSysDBOptJob.ApplyToTargetServer(srvMgmtServer.Name)
jobSysDBOptJob .StartStepID = intStepID
jobSysDBOptJob.Alter()
End If
' Build the failure notification step
strSysDBOptFailCmd = BuildNotifyStep("SystemDBOptimizations", _
intVersion, dbDatabase.Name)
i += 1
jbsSysDBOptFailStep = New JobStep(jobSysDBOptJob, "Step " + CStr(i))
jbsSysDBOptFailStep.DatabaseName = "master"
jbsSysDBOptFailStep.Command = strSysDBOptFailCmd
jbsSysDBOptFailStep.OnSuccessAction = StepCompletionAction.GoToNextStep
jbsSysDBOptFailStep.OnFailAction = StepCompletionAction.GoToNextStep
jbsSysDBOptFailStep.Create()
End If
End If
Next
i += 1
' Build a "dummy" step for the last maintenance step to branch to on success
jbsSysDBOptJobStep = New JobStep(jobSysDBOptJob, "Step " + CStr(i))
jbsSysDBOptJobStep.DatabaseName = "master"
jbsSysDBOptJobStep.Command = "select 1"
jbsSysDBOptJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess
jbsSysDBOptJobStep.OnFailAction = StepCompletionAction .QuitWithSuccess
jbsSysDBOptJobStep.Create()
Then we create the schedule object and set the properties to occur once a week, on Sunday at 3AM, starting today.
'Define a JobSchedule object variable by supplying the parent
'job and name arguments in the constructor.
jbschSysDBOptJobSched = New JobSchedule(jobSysDBOptJob, "Sched 01")
'Set properties to define the schedule frequency, and duration.
jbschSysDBOptJobSched.FrequencyTypes = FrequencyTypes.Weekly
jbschSysDBOptJobSched.FrequencyRecurrenceFactor = 1
jbschSysDBOptJobSched.FrequencySubDayTypes = FrequencySubDayTypes.Once
Dim tsSysDBOptStart As TimeSpan
tsSysDBOptStart = New TimeSpan(3, 0, 0)
jbschSysDBOptJobSched.ActiveStartTimeOfDay = tsSysDBOptStart
Dim tsSysDBOptEnd As TimeSpan
tsSysDBOptEnd = New TimeSpan(23, 59, 59)
jbschSysDBOptJobSched.ActiveEndTimeOfDay = tsSysDBOptEnd
jbschSysDBOptJobSched.FrequencyInterval = 1
Dim dtSysDBOptStartDate As Date = Date.Today
jbschSysDBOptJobSched.ActiveStartDate = dtSysDBOptStartDate
'Create the job schedule on the instance of SQL Agent.
jbschSysDBOptJobSched.Create()
The Catch block notifies us if any problems in the subroutine, and uses the same code as the Catch block in the main routine, and we’re done with the System Database Optimizations job.
End Sub
User Database Optimizations
I ran into a problem building the user database optimizations, because the command length for a job step didn’t allow me to include commands to update the statistics for all of the tables in a database with lots of tables and views. Because of this I built stored procedures in each of the databases with the necessary optimizations, and in the job I execute the stored procedure. We’ll start with the stored procedure to reorg the indexes.
Sub BuildIndexReorgProc(ByRef srvMgmtServer As Server, ByVal strDatabase As String)
Dim dbDatabase As Database ' The Database object
Dim objTableColl As TableCollection ' The table collection
Dim objTable As Table ' The table object (for iteration)
Dim objIndexColl As IndexCollection ' The index collection
Dim objIndex As Index ' The index object (for iteration)
Dim objViewColl As ViewCollection ' The view collection
Dim objView As View ' The view object (for iteration)
Dim spStoredProc As StoredProcedure ' The stored procedure object
Dim strSPText As String ' The text string for the stored procedure
Try
We need to set the initial fields so SMO performs well, get the database object for the selected database, and drop the existing stored procedure if it exists.
' Get the database object from the server and set the default init fields
srvMgmtServer.SetDefaultInitFields(GetType(Table), "Name")
srvMgmtServer.SetDefaultInitFields(GetType(View), "Name")
srvMgmtServer.SetDefaultInitFields(GetType(Index), "Name")
srvMgmtServer.SetDefaultInitFields(GetType(Table), "IsSystemObject")
srvMgmtServer.SetDefaultInitFields(GetType(View), "IsSystemObject")
dbDatabase = srvMgmtServer.Databases(strDatabase)
' Drop the stored procedure if it already exists
spStoredProc = dbDatabase.StoredProcedures("db_index_reorg")
If Not (spStoredProc Is Nothing) Then
spStoredProc.Drop()
End If
The stored procedure we’re creating in this routine is called db_index_reorg, because that’s what it does.
'Build the stored procedure for the index reorg
spStoredProc = New StoredProcedure(dbDatabase, "db_index_reorg")
spStoredProc.TextMode = False
spStoredProc.AnsiNullsStatus = False
spStoredProc.QuotedIdentifierStatus = False
strSPText = "" + vbCrLf
We’ll grab the collection of tables in the database and iterate through them, creating an ALTER INDEX command for each non-system table.
' Create an ALTER INDEX statement for each index in each table
objTableColl = dbDatabase.Tables
For Each objTable In objTableColl
If objTable.IsSystemObject = False Then
objIndexColl = objTable.Indexes
For Each objIndex In objIndexColl
strSPText = strSPText + "ALTER INDEX [" + objIndex.Name + _
"] ON [" + objTable.Schema + "].[" + objTable.Name + _
"] REORGANIZE WITH ( LOB_COMPACTION = ON )" + vbCrLf
Next
End If
Next
strSPText = strSPText + "" + vbCrLf
Since views can have indexes as well we need to do the same thing for each view.
' Create an ALTER INDEX statement for each index in each view
objViewColl = dbDatabase.Views
For Each objView In objViewColl
If objView.IsSystemObject = False Then
objIndexColl = objView.Indexes
For Each objIndex In objIndexColl
strSPText = strSPText + "ALTER INDEX [" + objView.Name + _
"] ON [" + objView.Schema + "].[" + objView.Name + _
"] REORGANIZE WITH ( LOB_COMPACTION = ON )" + vbCrLf
Next
End If
Next
strSPText = strSPText + "" + vbCrLf
The body of the stored procedure is now complete, so we can assign it to the textbody property of the stored procedure object, and create the stored procedure.
' Assign the stored procedure text body and create it
spStoredProc.TextBody = strSPText
spStoredProc.Create()
The Catch block uses the same code as the Catch block in the main routine, and we’re done with this subroutine.
End Sub
The subroutine to build the Update Statistics stored procedure is similar to the index reorg subroutine.
Sub BuildUpdateStatsProc(ByRef srvMgmtServer As Server, ByVal strDatabase As String)
Dim dbDatabase As Database ' The Database object
Dim objTableColl As TableCollection ' The table collection
Dim objTable As Table ' The table object (for iteration)
Dim objViewColl As ViewCollection ' The view collection
Dim objView As View ' The view object (for iteration)
Dim spStoredProc As StoredProcedure ' The stored procedure object
Dim strSPText As String ' The text string for the stored procedure
Try
We need to set the initial fields so SMO performs well, get the database object for the selected database, and drop the existing stored procedure if it exists.
' Get the database object from the server and set the default init fields
srvMgmtServer.SetDefaultInitFields(GetType(Table), "Name")
srvMgmtServer.SetDefaultInitFields(GetType(View), "Name")
srvMgmtServer.SetDefaultInitFields(GetType(Index), "Name")
srvMgmtServer.SetDefaultInitFields(GetType (Table), "IsSystemObject")
srvMgmtServer.SetDefaultInitFields(GetType(View), "IsSystemObject")
dbDatabase = srvMgmtServer.Databases(strDatabase)
spStoredProc = dbDatabase.StoredProcedures("db_update_stats")
If Not (spStoredProc Is Nothing) Then
spStoredProc.Drop()
End If
The stored procedure we’re creating in this routine is called db_update_stats.
'Build the stored procedure for the update stats
spStoredProc = New StoredProcedure(dbDatabase, "db_update_stats")
spStoredProc.TextMode = False
spStoredProc .AnsiNullsStatus = False
spStoredProc.QuotedIdentifierStatus = False
strSPText = "" + vbCrLf
We need to iterate through the table collection and build UPDATE STATISTICS statements for each non-system table in the database.
' Create an UPDATE STATISTICS statement for each table
objTableColl = dbDatabase.Tables
For Each objTable In objTableColl
If objTable.IsSystemObject = False Then
strSPText = strSPText + "UPDATE STATISTICS [" + _
objTable.Schema + "].[" + objTable.Name + _
"]" + vbCrLf
End If
Next
The same thing needs to be done for the views in the database.
' Create an UPDATE STATISTICS statement for each view
objViewColl = dbDatabase.Views
For Each objView In objViewColl
If objView.IsSystemObject = False Then
strSPText = strSPText + "UPDATE STATISTICS [" + _
objView.Schema + "].[" + objView.Name + _
"] WITH FULLSCAN ,NORECOMPUTE" + vbCrLf
End If
Next
strSPText = strSPText + "" + vbCrLf
The body of the stored procedure is now complete, so we can assign it to the textbody property of the stored procedure object, and create the stored procedure.
' Assign the stored procedure text body and create it
spStoredProc.TextBody = strSPText
spStoredProc.Create()
The Catch block uses the same code as the Catch block in the main routine, and we’re done with this subroutine as well.
End Sub
Now that the stored procedures have been created (well, the subroutines that will create the stored procedures) we can build the last job. This job will perform the optimizations for the user databases on the target server.
Sub BuildUserDBOpt(ByRef srvMgmtServer As Server)
Dim strJobID As Guid ' The JobID assigned to the created job
Dim intStepID As Integer ' The StepID for each step created in the job
Dim dbcDatabases As DatabaseCollection ' The target server database collection
Dim dbDatabase As Database ' The Database object for the current db
Dim intVersion As Integer ' The SQL Server version number
Dim jobUserDBOptJob As Job ' The Job object for the created job
Dim jbsUserDBOptJobStep As JobStep ' The JobStep object for the current step
Dim jbsUserDBOptFailStep As JobStep ' The JobStep object for the failure step
Dim jbschUserDBOptJobSched As JobSchedule ' The JobSchedule object for the schedule
Dim strUserDBOptFailCmd As String ' The string containing the failure command
Dim i As Integer ' An iterator
Try
Next, we’ll get the database collection on the server, set the default initial fields for more efficient SMO operations, and create the Agent job.
' Get the database collection from the server and set the default init fields
dbcDatabases = srvMgmtServer.Databases
srvMgmtServer.SetDefaultInitFields(GetType(Table), "Name")
srvMgmtServer.SetDefaultInitFields(GetType(View), "Name")
srvMgmtServer.SetDefaultInitFields(GetType(Index), "Name")
srvMgmtServer.SetDefaultInitFields(GetType(Table), "IsSystemObject")
srvMgmtServer.SetDefaultInitFields(GetType(View), "IsSystemObject")
'Determine the SQL Server Version
intVersion = CInt(Left(srvMgmtServer.Information.Version.ToString, 1))
'Create the UserDB Optimizations Job
jobUserDBOptJob = New Job(srvMgmtServer.JobServer, "UserDBOptimizations")
jobUserDBOptJob.Description = "UserDB Optimizations"
jobUserDBOptJob.Category = "[Uncategorized (Local)]"
jobUserDBOptJob.OwnerLoginName = "sa"
jobUserDBOptJob.Create()
strJobID = jobUserDBOptJob.JobID
i = 0
In this job we need to create steps for each database, but we don’t want to create steps for system databases or for snapshot databases. Snapshot databases only exist in SQL Server 2005, so we set up a Boolean variable to indicate that we want to create a step for the database, and initialize it to TRUE. If the database is a system database, or if this server is a SQL Server 2005 server and the database is a snapshot database, we’ll set that flag to FALSE. Then, if the flag is set to TRUE we’ll build the step in the job for the database.
For Each dbDatabase In dbcDatabases
Dim bolProcessDB As Boolean
bolProcessDB = True
If dbDatabase.IsSystemObject = True 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 strOptCmd As String
Now, we can build the optimization commands for the step, including a DBCC SHRINKDATABASE command as well as executing each of the two stored procedures we just created. We then add that step, and if it’s the first step in the job, set the job to execute this step first
.
i += 1
If intVersion = 9 Then
' Shrink Database Command
strOptCmd = "DBCC SHRINKDATABASE(N'" + dbDatabase.Name + _
"', 10, TRUNCATEONLY)" + vbCrLf
strOptCmd = strOptCmd + "" + vbCrLf
' Reorg Indexes
BuildIndexReorgProc(srvMgmtServer, dbDatabase.Name)
strOptCmd = strOptCmd + "exec db_index_reorg" + vbCrLf
strOptCmd = strOptCmd + "" + vbCrLf
' Update Statistics
BuildUpdateStatsProc(srvMgmtServer, dbDatabase.Name)
strOptCmd = strOptCmd + "exec db_update_stats" + vbCrLf
strOptCmd = strOptCmd + "" + vbCrLf
Else
strOptCmd = "EXECUTE master.dbo.xp_sqlmaint N'-D " + dbDatabase.Name + _
" -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '" + vbCrLf
End If
jbsUserDBOptJobStep = New JobStep(jobUserDBOptJob, "Step " + CStr(i))
jbsUserDBOptJobStep.DatabaseName = dbDatabase.Name
jbsUserDBOptJobStep.Command = strOptCmd
jbsUserDBOptJobStep.OnSuccessAction = StepCompletionAction.GoToStep
jbsUserDBOptJobStep.OnSuccessStep = i + 2
jbsUserDBOptJobStep.OnFailAction = StepCompletionAction.GoToNextStep
jbsUserDBOptJobStep.Create()
intStepID = jbsUserDBOptJobStep.ID
If i = 1 Then
jobUserDBOptJob.ApplyToTargetServer(srvMgmtServer.Name)
jobUserDBOptJob.StartStepID = intStepID
jobUserDBOptJob.Alter()
End If
We can now build the failure notification step for each step in the job, and add a dummy step for the last step to go to when it succeeds.
strUserDBOptFailCmd = BuildNotifyStep("UserDBOptimizations", _
intVersion, dbDatabase.Name)
i += 1
jbsUserDBOptFailStep = New JobStep(jobUserDBOptJob, "Step " + CStr(i ))
jbsUserDBOptFailStep.DatabaseName = "master"
jbsUserDBOptFailStep.Command = strUserDBOptFailCmd
jbsUserDBOptFailStep.OnSuccessAction = StepCompletionAction.GoToNextStep
jbsUserDBOptFailStep.OnFailAction = StepCompletionAction.GoToNextStep
jbsUserDBOptFailStep.Create()
End If
Next
i += 1
jbsUserDBOptJobStep = New JobStep(jobUserDBOptJob, "Step " + CStr(i))
jbsUserDBOptJobStep.DatabaseName = "master"
jbsUserDBOptJobStep.Command = "select 1"
jbsUserDBOptJobStep.OnSuccessAction = StepCompletionAction .QuitWithSuccess
jbsUserDBOptJobStep.OnFailAction = StepCompletionAction.QuitWithSuccess
jbsUserDBOptJobStep.Create()
Then we create the schedule object and set the properties to occur once a week, on Sunday at 3AM, starting today.
'Define a JobSchedule object variable by supplying the parent job
'and name arguments in the constructor.
jbschUserDBOptJobSched = New JobSchedule(jobUserDBOptJob, "Sched 01")
'Set properties to define the schedule frequency, and duration.
jbschUserDBOptJobSched.FrequencyTypes = FrequencyTypes.Weekly
jbschUserDBOptJobSched.FrequencyRecurrenceFactor = 1
jbschUserDBOptJobSched.FrequencySubDayTypes = FrequencySubDayTypes.Once
Dim tsUserDBOptStart As TimeSpan
tsUserDBOptStart = New TimeSpan(3, 0, 0)
jbschUserDBOptJobSched.ActiveStartTimeOfDay = tsUserDBOptStart
Dim tsUserDBOptEnd As TimeSpan
tsUserDBOptEnd = New TimeSpan(23, 59, 59)
jbschUserDBOptJobSched.ActiveEndTimeOfDay = tsUserDBOptEnd
jbschUserDBOptJobSched.FrequencyInterval = 1
Dim dtUserDBOptStartDate As Date = Date.Today
jbschUserDBOptJobSched.ActiveStartDate = dtUserDBOptStartDate
'Create the job schedule on the instance of SQL Agent.
jbschUserDBOptJobSched.Create()
The Catch block notifies us if any problems in the subroutine, and uses the same code as the Catch block in the main routine, and we’re done with the System Database Optimizations job.
End Sub
There are many different tasks you can perform using this method, and you should adjust the code to perform the tasks you find most appropriate. Keeping your databases clean will help you get the maximum performance out of them and will keep your users (and your boss) happy.
This article has been viewed 7409 times.