Click here to monitor SSC
  • Av rating:
  • Total votes: 14
  • Total comments: 5
Allen White

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.

Allen White

Author profile:

Allen is a Practice Leader for Upsearch in Northeast Ohio. He's been both a developer and an administrator so understands both perspectives towards database technology, and loves sharing his experiences and helping people learn how to use SQL Server. Allen has spent over 35 years in IT and has been using SQL Server since 1992 and is certified MCITP in SQL Server and MCT. Allen has been awarded Microsoft’s MVP Award for the last six years. He's President of the Ohio North SQL Server User's Group and maintains a blog at http://sqlblog.com/blogs/allen_white/default.aspx.

Search for other articles by Allen White

Rate this article:   Avg rating: from a total of 14 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: help for implimenting it
Posted by: Anonymous (not signed in)
Posted on: Saturday, September 22, 2007 at 8:28 AM
Message: sir i got the code but not able to use it in my application it gives some error so plz tell me how can i implement it in my application or what changes i have to made in this code to make it working

Subject: Implemtation Help
Posted by: Allen White (not signed in)
Posted on: Tuesday, September 25, 2007 at 6:23 AM
Message: Sorry, sir, but without details on the error it returns I'm unable to give advise on how to correct the problem. The code is running as is in my shop.

Subject: Especially helpfull for DBA professionals
Posted by: Anonymous (not signed in)
Posted on: Thursday, February 07, 2008 at 8:31 AM
Message: I browsed upto the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder). but after that where we have to write the code I can't understand. so if you will cooperate me then I fell it will help me a lot.

Thanks

Subject: why build queries...
Posted by: Anonymous (not signed in)
Posted on: Monday, June 30, 2008 at 5:11 PM
Message: when the SMO lib has an update stats and the index rebuild available?

Subject: ping back
Posted by: rajib.bahar (view profile)
Posted on: Thursday, December 17, 2009 at 12:55 PM
Message: ping back from http://www.rajib-bahar.com/rajib/BlogEngine.Web/post/2009/12/08/Combining-SMO-and-Powershell-to-Generate-SQL-Database-Schema.aspx

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.