{"id":305,"date":"2007-09-15T00:00:00","date_gmt":"2007-09-15T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/automate-your-database-integrity-checks-using-smo\/"},"modified":"2021-08-24T13:40:41","modified_gmt":"2021-08-24T13:40:41","slug":"automate-your-database-integrity-checks-using-smo","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/automate-your-database-integrity-checks-using-smo\/","title":{"rendered":"Automate your Database Integrity checks using SMO"},"content":{"rendered":"<h3>Automate your Database Maintenance using SMO &#8211; Part 3<\/h3>\n<p><em>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 &#8230;<br \/>\n<\/em><a href=\"http:\/\/www.simple-talk.com\/sql\/backup-and-recovery\/automate-your-database-maintenance-using-smo\/\">Automate your Database backups using SMO<\/a> and<br \/>\n<a href=\"http:\/\/www.simple-talk.com\/sql\/backup-and-recovery\/alert-based-transaction-log-backups---automate-your-database-maintenance-part-2\/\">Alert-based Transaction Log Backups<\/a><br \/>\n<em>(The source code to this article can be accessed via the speech-bubble above)<\/em><\/p>\n<p>Once you&#8217;ve implemented a well thought-out backup plan you&#8217;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.<\/p>\n<p>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.<\/p>\n<h3>Creating and running the VB Script<\/h3>\n<p>The first thing you&#8217;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):<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Microsoft.SqlServer.ConnectionInfo.dll\r\nMicrosoft.SqlServer.Smo.dll\r\nMicrosoft.SqlServer.SmoEnum.dll\r\nMicrosoft.SqlServer.SqlEnum.dll<\/pre>\n<p>Next, in the code window at the top of the code, before any declarations, insert the following lines:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Microsoft.SqlServer.Management.SMO\r\nImports Imports Microsoft.SqlServer.Management.Common\r\nImports Microsoft.SqlServer.Management.SMO.Agent\r\n\r\nModule Module1<\/pre>\n<p>You&#8217;ll also need to change the Sub Main() statement to:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Sub Main(ByVal CmdArgs() As String)<\/pre>\n<p>This change will allow you to process the command-line arguments, which is the first thing we need to do in each application.<\/p>\n<p>Most of my servers use named instances so I\u00a0can&#8217;t just tell the program to connect to\u00a0&#8220;.&#8221;. Therefore, I\u00a0pass in the server name as the first parameter.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Dim strServerName As String\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim i As Integer\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim intVersion As Integer\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim strNotificationEmail As String\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim strNotificationSource As String 'who notified?\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim strNotificationSMTP As String 'What was the SMTP (not SQL 2000)?\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strServerName = \".\"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The name of the server we're connecting to\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strNotificationEmail = \"Me@MyOrganisation.com\"\u00a0'who do we notify?\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strNotificationSource = \"MyServer@MyOrganisation.com\"\u00a0'who notified?\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strNotificationSMTP = \"smtp.MyOrganisation.com\"\u00a0'What was the SMTP (not SQL 2000)?\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0For i = 0 To UBound(CmdArgs)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim strCmdArg As String\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strCmdArg = CmdArgs(i)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If Left(strCmdArg, 1) = \"-\"\u00a0Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Select Case Mid(strCmdArg, 2, 1)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Case \"S\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strServerName = Mid(strCmdArg, 3, Len(strCmdArg) -\u00a02)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Case \"T\"\u00a0\u00a0\u00a0\u00a0'Email: who do we notify?\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strNotificationEmail = Mid(strCmdArg, 3, Len(strCmdArg) -\u00a02)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Case \"F\"\u00a0\u00a0\u00a0\u00a0'From Whom: who was the source?\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strNotificationSource = Mid(strCmdArg, 3, Len(strCmdArg) -\u00a02)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Case \" E\"\u00a0\u00a0\u00a0\u00a0'what SMTP server?\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strNotificationSMTP = Mid(strCmdArg, 3, Len(strCmdArg) -\u00a02)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\nEnd Select\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Next<\/pre>\n<p>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):<\/p>\n<p>\n\u00a0\u00a0u:\\DBMaint\\ BuildWeeklyMaintJobs -S&#8221;MyServer\\Inst01&#8243;\u00a0-Tmyname@myorg.com<\/p>\n<p>\nThe job will connect to server\u00a0&#8220;MyServer\\Inst01&#8221;,\u00a0\u00a0and notify mynam@myorg.com of the backup.<\/p>\n<p>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&#8217;re connecting to.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Try\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0' Connect to the server\r\n\u00a0\u00a0\u00a0\u00a0\u00a0Dim srvMgmtServer As Server\r\n\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer = New Server(strServerName)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0Dim srvConn As ServerConnection\r\n\u00a0\u00a0\u00a0\u00a0\u00a0srvConn = srvMgmtServer.ConnectionContext\r\n\u00a0\u00a0\u00a0\u00a0\u00a0srvConn.LoginSecure = True\r\n\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(Database), \"IsSystemObject\")\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0' Determine the SQL Server Version\r\n\u00a0\u00a0\u00a0\u00a0\u00a0intVersion = CInt(Left(srvMgmtServer.Information.Version.ToString, 1))\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0' Set the initial default fields to improve SMO performance\r\n\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(Table), \"Name\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(View), \"Name\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(Index), \"Name\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(Table), \"IsSystemObject\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(View) , \"IsSystemObject\")<\/pre>\n<p>The next step is to drop the existing maintenance jobs.\u00a0\u00a0I create separate jobs for system databases and user databases for optimizations, and one job for checking database integrity.\u00a0\u00a0The four job names I\u00a0use are\u00a0&#8216;CheckDBIntegrity&#8217;,\u00a0&#8216;SystemDBOptimizations&#8217;\u00a0and\u00a0&#8216;UserDBOptimizations&#8217;.\u00a0\u00a0These commands drop them so the rest of the program and build new copies.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0\u00a0'Drop the existing optimization and integrity jobs job\r\n\u00a0\u00a0\u00a0\u00a0\u00a0tblServerJobs = srvvMgmtServer.JobServer.EnumJobs\r\n\u00a0\u00a0\u00a0\u00a0\u00a0For Each rowServerJobs In tblServerJobs.Rows\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If rowServerJobs(\"Name\") = \"CheckDBIntegrity\"\u00a0_\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Or rowServerJobs(\"Name\") = \"SystemDBOptimizations\"\u00a0_\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Or rowServerJobs(\"Name\") = \"UserDBOptimizations\"\u00a0Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobDumpJob = srvMgmtServer.JobServer.Jobs(rowServerJobs(\"Name\"))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jobDumpJob.Drop()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If\r\n\u00a0\u00a0\u00a0\u00a0\u00a0Next<\/pre>\n<p>Now we&#8217;re ready to perform the subroutines that build the three jobs:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0\u00a0BuildDBIntegrity (srvMgmtServer)\u00a0\u00a0' Build CheckDBIntegrity job\r\n\u00a0\u00a0\u00a0\u00a0\u00a0BuildSystemDBOpt(srvMgmtServer)\u00a0\u00a0' Build SystemDBOptimizations job\r\n\u00a0\u00a0\u00a0\u00a0\u00a0BuildUserDBOpt(srvMgmtServer)\u00a0\u00a0\u00a0\u00a0' Build UserDBOptimizations job<\/pre>\n<p>This is the end of the main subroutine, so we want to catch any errors that might have occurred, using the following code:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">Catch smoex As SmoException\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Console.WriteLine(\"There has been an SMO error\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Display the SMO exception message.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Console.WriteLine(smoex.Message)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Display the sequence of non-SMO exceptions that caused the SMO exception.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim ex As Exception\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ex = smoex .InnerException\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Do While ex.InnerException IsNot (Nothing)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Console.WriteLine(ex.InnerException.Message)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ex = ex.InnerException\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Loop\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Catch other non-SMO exceptions.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Catch ex As Exception\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Console.WriteLine(\"There has been a\u00a0VB error.\u00a0\"\u00a0+ ex.Message)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Do While ex.InnerException IsNot (Nothing)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Console.WriteLine(ex.InnerException.Message)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ex = ex.InnerException\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Loop\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End Try\r\n\u00a0\u00a0\u00a0\u00a0End Sub<\/pre>\n<p>We also need the BuildNotifyStep module we used in parts 1\u00a0and 2.\u00a0\u00a0It can be included here or at the end of the module.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0Private Function BuildNotifyStep( _ \r\n\u00a0\u00a0\u00a0\u00a0\u00a0ByVal strJobName As String, _ \r\n\u00a0\u00a0\u00a0\u00a0\u00a0ByVal intVersion As Integer, _ \r\n\u00a0\u00a0\u00a0\u00a0\u00a0ByVal strDBName As String, _ \r\n\u00a0\u00a0\u00a0\u00a0\u00a0ByVal strNotificationEmail As String, _ \r\n\u00a0\u00a0\u00a0\u00a0\u00a0ByVal strNotificationSource As String, _ \r\n\u00a0\u00a0\u00a0\u00a0\u00a0ByVal strNotificationSMTP As String) As String\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0' This function was described in Part 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0End Function<\/pre>\n<h3>Creating a\u00a0job to Check Database Integrity<\/h3>\n<p>The first job we&#8217;ll create is the one which checks the database integrity of all databases on our server, except tempdb.\u00a0\u00a0(If you&#8217;re concerned about the integrity of tempdb, restart your server.\u00a0\u00a0It&#8217;ll be refreshed from model and you&#8217;ll have a\u00a0good tempdb again.)\u00a0\u00a0We use the DBCC CHECKDB WITH NO_INFOMSGS command to verify the integrity of each database on the server.\u00a0\u00a0First we&#8217;ll declare our subroutine and the variables it requires.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0Sub BuildDBIntegrity(ByRef srvMgmtServer As Server)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim strJobID As Guid\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The JobID assigned to the job we're creating\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim intStepID As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The StepID for each step created in the job\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim dbcDatabases As DatabaseCollection ' The database collection on the target server\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim dbDatabase As Database\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The Database object for the current database\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim intVersion As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The SQL Server version number\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim jobDBIntJob As Job\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The Job object for the created job\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim jbsDBIntJobStep As JobStep\u00a0\u00a0\u00a0\u00a0' The JobStep object for the current step\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim jbsDBIntFailStep As JobStep\u00a0\u00a0\u00a0' The JobStep object for the failure step\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim jbschDBIntJobSched As JobSchedule ' The JobSchedule object for the schedule\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim strDBIntFailCmd As String\u00a0\u00a0\u00a0\u00a0\u00a0' The string with the command to execute on failure\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim i As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' An iterator\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Try<\/pre>\n<p>Next, we&#8217;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.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Get the database collection from the server and set the default init fields\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0dbcDatabases = srvMgmtServer.Databases\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(Table), \"Name\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(View), \"Name\" )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(Index), \"Name\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(Table), \"IsSystemObject\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(View), \"IsSystemObject\")\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Determine the SQL Server Version\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0intVersion = CInt(Left(srvMgmtServer.Information.Version.ToString, 1))\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Create the Check DB Integrity Job\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobDBIntJob = New Job(srvMgmtServer.JobServer, \"CheckDBIntegrity\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobDBIntJob.Description = \"Check DB Integrity\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobDBIntJob.Category = \"[Uncategorized (Local)]\" \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobDBIntJob.OwnerLoginName = \"sa\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobDBIntJob.Create()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strJobID = jobDBIntJob.JobID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0i = 0<\/pre>\n<p>We have to step through each database object in the collection, and for each database (except tempdb) create a\u00a0step to run the DBCC command against the database.\u00a0\u00a0We&#8217;ll also set the starting step for the job.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' We need a\u00a0checkdb step and a\u00a0failure step for each database except tempdb\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0For Each dbDatabase In dbcDatabases\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If dbDatabase.Name &lt;&gt;\u00a0\"tempdb\"\u00a0Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0i += 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntJobStep = New JobStep(jobDBIntJob, \"Step\u00a0\"\u00a0+ CStr(i))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsDBIntJobStep.DatabaseName = dbDatabase.Name\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntJobStep.Command = \"DBCC CHECKDB WITH NO_INFOMSGS\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntJobStep.OnSuccessAction = StepCompletionAction.GoToStep\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntJobStep.OnSuccessStep = i + 2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntJobStep.OnFailAction = StepCompletionAction.GoToNextStep\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntJobStep.Create()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0intStepID = jbsDBIntJobStep.ID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If i = 1 Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobDBIntJob.ApplyToTargetServer(srvMgmtServer.Name)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobDBIntJob.StartStepID = intStepID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobDBIntJob.Alter()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If<\/pre>\n<p>We now build the step to notify us if the DBCC fails, and add a\u00a0dummy step for the last database&#8217;s DBCC step to go to when it succeeds.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">strDBIntFailCmd = BuildNotifyStep(\"CheckDBIntegrity\", intVersion, _ dbDatabase.Name)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0i += 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntFailStep = New JobStep(jobDBIntJob, \"Step\u00a0\"\u00a0+ CStr(i))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntFailStep.DatabaseName = \"master\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntFailStep.Command = strDBIntFailCmd\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntFailStep.OnSuccessAction = StepCompletionAction.GoToNextStep\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntFailStep.OnFailAction = StepCompletionAction.GoToNextStep\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntFailStep.Create()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Next\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Here we add a\u00a0\"dummy\"\u00a0step for the last checkdb step to go to on success\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0i += 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntJobStep = New JobStep(jobDBIntJob, \"Step\u00a0\"\u00a0+ CStr(i))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntJobStep.DatabaseName = \"master\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntJobStep.Command = \"select 1\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntJobStep.OnFailAction = StepCompletionAction.QuitWithSuccess\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsDBIntJobStep.Create()<\/pre>\n<p>Then we create the schedule object and set the properties to occur once a\u00a0week, on Sunday at midnight, starting today.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Define a\u00a0JobSchedule object variable by supplying the parent job\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'and name arguments in the constructor. \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschDBIntJobSched = New JobSchedule(jobDBIntJob, \"Sched 01\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Set properties to define the schedule frequency, and duration.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschDBIntJobSched.FrequencyTypes = FrequencyTypes.Weekly\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschDBIntJobSched.FrequencyRecurrenceFactor = 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschDBIntJobSched.FrequencySubDayTypes = FrequencySubDayTypes.Once\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim tsDBIntStart As TimeSpan\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0tsDBIntStart = New TimeSpan(0, 0, 0)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschDBIntJobSched.ActiveStartTimeOfDay = tsDBIntStart\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim tsDBIntEnd As TimeSpan\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0tsDBIntEnd = New TimeSpan(23, 59, 59)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschDBIntJobSched.ActiveEndTimeOfDay = tsDBIntEnd\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschDBIntJobSched.FrequencyInterval = 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim dtDBIntStartDate As Date =\u00a0Date.Today\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschDBIntJobSched.ActiveStartDate = dtDBIntStartDate\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Create the job schedule on the instance of SQL Agent.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschDBIntJobSched.Create()<\/pre>\n<p>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&#8217;re done with the Check Integrity job.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\r\n\u00a0\u00a0\u00a0\u00a0End Sub<\/pre>\n<h3>System Database Optimizations<\/h3>\n<p>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.\u00a0\u00a0Your optimizations can include the tasks you find most useful.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\r\n\u00a0\u00a0\u00a0\u00a0Sub BuildSystemDBOpt(ByRef srvMgmtServer As Server)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim strJobID As Guid\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The JobID assigned to the job we're creating\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim intStepID As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The StepID for each step created in the job\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim dbcDatabases As DatabaseCollection\u00a0\u00a0' The database collection on the target server\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim dbDatabase As Database\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The Database object for the current database\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim intVersion As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The SQL Server version number\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim jobSysDBOptJob As Job\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The Job object for the created job\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim jbsSysDBOptJobStep As JobStep\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The JobStep object for the current step\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim jbsSysDBOptFailStep As JobStep\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The JobStep object for the failure step\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim jbschSysDBOptJobSched As JobSchedule\u00a0\u00a0\u00a0\u00a0' The JobSchedule object for the schedule \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim strSysDBOptFailCmd As String ' The string with the command to execute on failure\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim i As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' An iterator\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Try<\/pre>\n<p>Next, we&#8217;ll get the database collection on the server, set the default initial fields for more efficient SMO operations, and create the Agent job.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Get the database collection from the server and set the default init fields\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0dbcDatabases = srvMgmtServer.Databases\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(Table), \"Name\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(View), \"Name\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(Index), \"Name\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType( Table), \"IsSystemObject\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(View), \"IsSystemObject\")\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Determine the SQL Server Version\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0intVersion = CInt(Left(srvMgmtServer.Information.Version.ToString, 1))\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Create the SystemDB Optimizations Job\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobSysDBOptJob = New Job(srvMgmtServer.JobServer, \"SystemDBOptimizations\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobSysDBOptJob.Description = \"SystemDB Optimizations\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobSysDBOptJob.Category = \"[Uncategorized (Local)]\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobSysDBOptJob.OwnerLoginName = \"sa\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobSysDBOptJob.Create()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strJobID = jobSysDBOptJob.JobID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i = 0<\/pre>\n<p>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.\u00a0\u00a0These same actions are performed in SQL Server 2005 via the DBCC SHRINKDATABASE and UPDATE STATISTICS commands.\u00a0\u00a0<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' In each database we want to shrink the database, leaving 10 percent free space,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' for each table within the database we want to update statistics, and for each view\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' we want to update statistics with the FULLSCAN and NORECOMPUTE options.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0For Each dbDatabase In dbcDatabases\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If dbDatabase.IsSystemObject = True Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If dbDatabase.Name &lt;&gt;\u00a0\"tempdb\"\u00a0Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim strOptCmd As String\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The optimizations command string\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim objTableColl As TableCollection ' The collection of tables \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim objTable As Table\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' A\u00a0table object for iteration\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim objViewColl As ViewCollection\u00a0\u00a0\u00a0' The collection of views\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim objView As View\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' A\u00a0view object for iteration\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0i += 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If intVersion = 9 Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Shrink Database Command\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strOptCmd = \"DBCC SHRINKDATABASE(N'\"\u00a0+ dbDatabase.Name + \"', _\r\n10, TRUNCATEONLY)\"\u00a0+ vbCrLf\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 strOptCmd = strOptCmd + \"\"\u00a0+ vbCrLf\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0objTableColl = dbDatabase.Tables\u00a0\u00a0\u00a0\u00a0' Get the database tables \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0objViewColl = dbDatabase.Views\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Get the database views \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Update Statistics\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0For Each objTable In objTableColl\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If objTable.IsSystemObject = False Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strOptCmd = strOptCmd + \"UPDATE STATISTICS [\"\u00a0+ _\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0objTable.Schema + \"].[\"\u00a0+ objTable.Name + \"]\"\u00a0+ vbCrLf\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Next\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0For Each objView In objViewColl\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If objView.IsSystemObject = False Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strOptCmd = strOptCmd + \"UPDATE STATISTICS [\"\u00a0+ _\r\nobjView.Schema + \"].[\"\u00a0+ objView.Name + \"] _\r\nWITH FULLSCAN ,NORECOMPUTE\"\u00a0+ vbCrLf\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Next\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strOptCmd = strOptCmd + \"\"\u00a0+ vbCrLf \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Else\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' xp_sqlmaint handles this maintenance in SQL Server 2000 servers\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strOptCmd = \"EXECUTE master.dbo.xp_sqlmaint N'-D\u00a0\"\u00a0+ _\r\ndbDatabase.Name + _\r\n\"\u00a0-WriteHistory\u00a0\u00a0-RebldIdx 10 -RmUnusedSpace 50 10 '\"\u00a0+ vbCrLf\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Add the Job Step, and assign the command just built to the step\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptJobStep = New JobStep(jobSysDBOptJob, \"Step\u00a0\"\u00a0+ CStr(i))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptJobStep.DatabaseName = dbDatabase.Name\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptJobStep.Command = strOptCmd\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptJobStep.OnSuccessAction = StepCompletionAction.GoToStep\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptJobStep.OnSuccessStep = i + 2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptJobStep.OnFailAction = StepCompletionAction.GoToNextStep\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptJobStep.Create()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0intStepID = jbsSysDBOptJobStep.ID<\/pre>\n<p>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\u00a0dummy step for the last step to go to when it succeeds.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">.\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If i = 1 Then\u00a0\u00a0' If this is the first job step, assign it to the job\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobSysDBOptJob.ApplyToTargetServer(srvMgmtServer.Name)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobSysDBOptJob .StartStepID = intStepID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobSysDBOptJob.Alter()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Build the failure notification step\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strSysDBOptFailCmd = BuildNotifyStep(\"SystemDBOptimizations\", _\r\nintVersion, dbDatabase.Name)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0i += 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptFailStep = New JobStep(jobSysDBOptJob, \"Step\u00a0\"\u00a0+ CStr(i))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptFailStep.DatabaseName = \"master\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptFailStep.Command = strSysDBOptFailCmd\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbsSysDBOptFailStep.OnSuccessAction = StepCompletionAction.GoToNextStep\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptFailStep.OnFailAction = StepCompletionAction.GoToNextStep\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptFailStep.Create()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Next\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0i += 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Build a\u00a0\"dummy\"\u00a0step for the last maintenance step to branch to on success\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptJobStep = New JobStep(jobSysDBOptJob, \"Step\u00a0\"\u00a0+ CStr(i))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptJobStep.DatabaseName = \"master\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptJobStep.Command = \"select 1\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptJobStep.OnSuccessAction = StepCompletionAction.QuitWithSuccess\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptJobStep.OnFailAction = StepCompletionAction .QuitWithSuccess\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsSysDBOptJobStep.Create()<\/pre>\n<p>Then we create the schedule object and set the properties to occur once a\u00a0week, on Sunday at 3AM, starting today.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Define a\u00a0JobSchedule object variable by supplying the parent\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'job and name arguments in the constructor. \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschSysDBOptJobSched = New JobSchedule(jobSysDBOptJob, \"Sched 01\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Set properties to define the schedule frequency, and duration.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschSysDBOptJobSched.FrequencyTypes = FrequencyTypes.Weekly\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschSysDBOptJobSched.FrequencyRecurrenceFactor = 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschSysDBOptJobSched.FrequencySubDayTypes = FrequencySubDayTypes.Once\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim tsSysDBOptStart As TimeSpan\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0tsSysDBOptStart = New TimeSpan(3, 0, 0)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschSysDBOptJobSched.ActiveStartTimeOfDay = tsSysDBOptStart\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Dim tsSysDBOptEnd As TimeSpan\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0tsSysDBOptEnd = New TimeSpan(23, 59, 59)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschSysDBOptJobSched.ActiveEndTimeOfDay = tsSysDBOptEnd\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschSysDBOptJobSched.FrequencyInterval = 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim dtSysDBOptStartDate As Date =\u00a0Date.Today\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschSysDBOptJobSched.ActiveStartDate = dtSysDBOptStartDate\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Create the job schedule on the instance of SQL Agent.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschSysDBOptJobSched.Create()<\/pre>\n<p>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&#8217;re done with the System Database Optimizations job.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0End Sub<\/pre>\n<h3>User Database Optimizations<\/h3>\n<p>I ran into a\u00a0problem building the user database optimizations, because the command length for a\u00a0job step didn&#8217;t allow me to include commands to update the statistics for all of the tables in a\u00a0database with lots of tables and views.\u00a0\u00a0Because of this I\u00a0built stored procedures in each of the databases with the necessary optimizations, and in the job I\u00a0execute the stored procedure.\u00a0\u00a0We&#8217;ll start with the stored procedure to reorg the indexes.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0Sub BuildIndexReorgProc(ByRef srvMgmtServer As Server, ByVal strDatabase As String)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim dbDatabase As Database\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The Database object\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim objTableColl As TableCollection ' The table collection\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim objTable As Table\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The table object (for iteration)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim objIndexColl As IndexCollection ' The index collection\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim objIndex As Index\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The index object (for iteration)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim objViewColl As ViewCollection\u00a0\u00a0\u00a0' The view collection\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim objView As View\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The view object (for iteration) \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim spStoredProc As StoredProcedure ' The stored procedure object\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim strSPText As String\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The text string for the stored procedure\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Try<\/pre>\n<p>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.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Get the database object from the server and set the default init fields\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(Table), \"Name\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(View), \"Name\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(Index), \"Name\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(Table), \"IsSystemObject\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 srvMgmtServer.SetDefaultInitFields(GetType(View), \"IsSystemObject\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0dbDatabase = srvMgmtServer.Databases(strDatabase)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Drop the stored procedure if it already exists\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0spStoredProc = dbDatabase.StoredProcedures(\"db_index_reorg\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If Not (spStoredProc Is Nothing) Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0spStoredProc.Drop()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If<\/pre>\n<p>The stored procedure we&#8217;re creating in this routine is called db_index_reorg, because that&#8217;s what it does.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Build the stored procedure for the index reorg\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0spStoredProc = New StoredProcedure(dbDatabase, \"db_index_reorg\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0spStoredProc.TextMode = False\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0spStoredProc.AnsiNullsStatus = False\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0spStoredProc.QuotedIdentifierStatus = False\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strSPText = \"\"\u00a0+ vbCrLf<\/pre>\n<p>We&#8217;ll grab the collection of tables in the database and iterate through them, creating an ALTER INDEX command for each non-system table.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Create an ALTER INDEX statement for each index in each table\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0objTableColl = dbDatabase.Tables\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0For Each objTable In objTableColl\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If objTable.IsSystemObject = False Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0objIndexColl = objTable.Indexes\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0For Each objIndex In objIndexColl\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strSPText = strSPText + \"ALTER INDEX [\"\u00a0+ objIndex.Name + _\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"] ON [\"\u00a0+ objTable.Schema + \"].[\"\u00a0+ objTable.Name + _\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"] REORGANIZE WITH (\u00a0LOB_COMPACTION =\u00a0ON )\"\u00a0+ vbCrLf\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Next\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Next\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strSPText = strSPText + \"\"\u00a0+ vbCrLf<\/pre>\n<p>Since views can have indexes as well we need to do the same thing for each view.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Create an ALTER INDEX statement for each index in each view\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0objViewColl = dbDatabase.Views\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0For Each objView In objViewColl\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If objView.IsSystemObject = False Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0objIndexColl = objView.Indexes\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 For Each objIndex In objIndexColl\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strSPText = strSPText + \"ALTER INDEX [\"\u00a0+ objView.Name + _\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"] ON [\"\u00a0+ objView.Schema + \"].[\"\u00a0+ objView.Name + _\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"] REORGANIZE WITH (\u00a0LOB_COMPACTION =\u00a0ON )\"\u00a0+ vbCrLf\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Next\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Next\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strSPText = strSPText + \"\"\u00a0+ vbCrLf<\/pre>\n<p>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.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Assign the stored procedure text body and create it \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0spStoredProc.TextBody = strSPText\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0spStoredProc.Create()<\/pre>\n<p>The Catch block uses the same code as the Catch block in the main routine, and we&#8217;re done with this subroutine.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\r\n\u00a0\u00a0\u00a0\u00a0End Sub<\/pre>\n<p>The subroutine to build the Update Statistics stored procedure is similar to the index reorg subroutine.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\r\n\u00a0\u00a0\u00a0\u00a0Sub BuildUpdateStatsProc(ByRef srvMgmtServer As Server, ByVal strDatabase As String)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim dbDatabase As Database\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The Database object\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim objTableColl As TableCollection ' The table collection\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim objTable As Table\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The table object (for iteration)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim objViewColl As ViewCollection\u00a0\u00a0\u00a0' The view collection\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim objView As View\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The view object (for iteration)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim spStoredProc As StoredProcedure ' The stored procedure object\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim strSPText As String\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The text string for the stored procedure\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Try<\/pre>\n<p>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.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Get the database object from the server and set the default init fields\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(Table), \"Name\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(View), \"Name\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(Index), \"Name\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType (Table), \"IsSystemObject\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(View), \"IsSystemObject\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0dbDatabase = srvMgmtServer.Databases(strDatabase)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0spStoredProc = dbDatabase.StoredProcedures(\"db_update_stats\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If Not (spStoredProc Is Nothing) Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0spStoredProc.Drop()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If<\/pre>\n<p>The stored procedure we&#8217;re creating in this routine is called db_update_stats.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Build the stored procedure for the update stats\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0spStoredProc = New StoredProcedure(dbDatabase, \"db_update_stats\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0spStoredProc.TextMode = False\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0spStoredProc .AnsiNullsStatus = False\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0spStoredProc.QuotedIdentifierStatus = False\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strSPText = \"\"\u00a0+ vbCrLf<\/pre>\n<p>We need to iterate through the table collection and build UPDATE STATISTICS statements for each non-system table in the database.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Create an UPDATE STATISTICS statement for each table\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0objTableColl = dbDatabase.Tables\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0For Each objTable In objTableColl\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If objTable.IsSystemObject = False Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strSPText = strSPText + \"UPDATE STATISTICS [\"\u00a0+ _\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0objTable.Schema + \"].[\"\u00a0+ objTable.Name + _\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"]\"\u00a0+ vbCrLf\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 End If\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Next<\/pre>\n<p>The same thing needs to be done for the views in the database.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Create an UPDATE STATISTICS statement for each view\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0objViewColl = dbDatabase.Views\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0For Each objView In objViewColl\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If objView.IsSystemObject = False Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strSPText = strSPText + \"UPDATE STATISTICS [\"\u00a0+ _\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0objView.Schema + \"].[\"\u00a0+ objView.Name + _\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"] WITH FULLSCAN ,NORECOMPUTE\"\u00a0+ vbCrLf\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Next\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strSPText = strSPText + \"\"\u00a0+ vbCrLf <\/pre>\n<p>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.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Assign the stored procedure text body and create it\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0spStoredProc.TextBody = strSPText\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0spStoredProc.Create()<\/pre>\n<p>The Catch block uses the same code as the Catch block in the main routine, and we&#8217;re done with this subroutine as well.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0End Sub<\/pre>\n<p>Now that the stored procedures have been created (well, the subroutines that will create the stored procedures) we can build the last job.\u00a0\u00a0This job will perform the optimizations for the user databases on the target server.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0Sub BuildUserDBOpt(ByRef srvMgmtServer As Server)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim strJobID As Guid\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The JobID assigned to the created job \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim intStepID As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The StepID for each step created in the job\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim dbcDatabases As DatabaseCollection\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The target server database collection \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim dbDatabase As Database\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The Database object for the current db\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim intVersion As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The SQL Server version number\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim jobUserDBOptJob As Job\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The Job object for the created job\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim jbsUserDBOptJobStep As JobStep\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The JobStep object for the current step\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim jbsUserDBOptFailStep As JobStep\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The JobStep object for the failure step\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim jbschUserDBOptJobSched As JobSchedule\u00a0\u00a0\u00a0' The JobSchedule object for the schedule\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim strUserDBOptFailCmd As String\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' The string containing the failure command \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim i As Integer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' An iterator\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Try<\/pre>\n<p>Next, we&#8217;ll get the database collection on the server, set the default initial fields for more efficient SMO operations, and create the Agent job.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Get the database collection from the server and set the default init fields\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0dbcDatabases = srvMgmtServer.Databases\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(Table), \"Name\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(View), \"Name\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(Index), \"Name\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0srvMgmtServer.SetDefaultInitFields(GetType(Table), \"IsSystemObject\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 srvMgmtServer.SetDefaultInitFields(GetType(View), \"IsSystemObject\")\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Determine the SQL Server Version\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0intVersion = CInt(Left(srvMgmtServer.Information.Version.ToString, 1))\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Create the UserDB Optimizations Job\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobUserDBOptJob = New Job(srvMgmtServer.JobServer, \"UserDBOptimizations\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobUserDBOptJob.Description = \"UserDB Optimizations\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobUserDBOptJob.Category = \"[Uncategorized (Local)]\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobUserDBOptJob.OwnerLoginName = \"sa\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobUserDBOptJob.Create()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strJobID = jobUserDBOptJob.JobID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0i = 0<\/pre>\n<p>In this job we need to create steps for each database, but we don&#8217;t want to create steps for system databases or for snapshot databases.\u00a0\u00a0Snapshot databases only exist in SQL Server 2005, so we set up a\u00a0Boolean variable to indicate that we want to create a\u00a0step for the database, and initialize it to TRUE.\u00a0\u00a0If the database is a\u00a0system database, or if this server is a\u00a0SQL Server 2005 server and the database is a\u00a0snapshot database, we&#8217;ll set that flag to FALSE.\u00a0\u00a0Then, if the flag is set to TRUE we&#8217;ll build the step in the job for the database.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0For Each dbDatabase In dbcDatabases\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim bolProcessDB As Boolean\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0bolProcessDB = True\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If dbDatabase.IsSystemObject = True Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0bolProcessDB = False\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If dbDatabase.CompatibilityLevel = CompatibilityLevel.Version90 Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If dbDatabase.IsDatabaseSnapshot Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0bolProcessDB = False\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If bolProcessDB = True Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim strOptCmd As String<\/pre>\n<p>Now, we can build the optimization commands for the step, including\u00a0\u00a0a DBCC SHRINKDATABASE command as well as executing each of the two stored procedures we just created.\u00a0\u00a0We then add that step, and if it&#8217;s the first step in the job, set the job to execute this step first<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">.\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0i += 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0If intVersion = 9 Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Shrink Database Command\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strOptCmd = \"DBCC SHRINKDATABASE(N'\"\u00a0+ dbDatabase.Name + _\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"', 10, TRUNCATEONLY)\"\u00a0+ vbCrLf\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strOptCmd = strOptCmd + \"\"\u00a0+ vbCrLf\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Reorg Indexes\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0BuildIndexReorgProc(srvMgmtServer, dbDatabase.Name)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strOptCmd = strOptCmd + \"exec db_index_reorg\"\u00a0+ vbCrLf\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strOptCmd = strOptCmd + \"\"\u00a0+ vbCrLf\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0' Update Statistics\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0BuildUpdateStatsProc(srvMgmtServer, dbDatabase.Name)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strOptCmd = strOptCmd + \"exec db_update_stats\"\u00a0+ vbCrLf\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strOptCmd = strOptCmd + \"\"\u00a0+ vbCrLf\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Else\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strOptCmd = \"EXECUTE master.dbo.xp_sqlmaint N'-D\u00a0\"\u00a0+ dbDatabase.Name + _\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0-WriteHistory\u00a0\u00a0-RebldIdx 10 -RmUnusedSpace 50 10 '\"\u00a0+ vbCrLf\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptJobStep = New JobStep(jobUserDBOptJob, \"Step\u00a0\"\u00a0+ CStr(i))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptJobStep.DatabaseName = dbDatabase.Name\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptJobStep.Command = strOptCmd\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptJobStep.OnSuccessAction = StepCompletionAction.GoToStep\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptJobStep.OnSuccessStep = i + 2\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptJobStep.OnFailAction = StepCompletionAction.GoToNextStep\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptJobStep.Create()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0intStepID = jbsUserDBOptJobStep.ID\r\n\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 If i = 1 Then\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobUserDBOptJob.ApplyToTargetServer(srvMgmtServer.Name)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobUserDBOptJob.StartStepID = intStepID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jobUserDBOptJob.Alter()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If<\/pre>\n<p>We can now build the failure notification step for each step in the job, and add a\u00a0dummy step for the last step to go to when it succeeds.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0strUserDBOptFailCmd = BuildNotifyStep(\"UserDBOptimizations\", _\r\nintVersion, dbDatabase.Name)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0i += 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptFailStep = New JobStep(jobUserDBOptJob, \"Step\u00a0\"\u00a0+ CStr(i ))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptFailStep.DatabaseName = \"master\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptFailStep.Command = strUserDBOptFailCmd\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptFailStep.OnSuccessAction = StepCompletionAction.GoToNextStep\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptFailStep.OnFailAction = StepCompletionAction.GoToNextStep\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptFailStep.Create()\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0End If\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Next\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0i += 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptJobStep = New JobStep(jobUserDBOptJob, \"Step\u00a0\"\u00a0+ CStr(i))\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptJobStep.DatabaseName = \"master\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptJobStep.Command = \"select 1\"\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptJobStep.OnSuccessAction = StepCompletionAction .QuitWithSuccess\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptJobStep.OnFailAction = StepCompletionAction.QuitWithSuccess\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbsUserDBOptJobStep.Create()<\/pre>\n<p>Then we create the schedule object and set the properties to occur once a\u00a0week, on Sunday at 3AM, starting today.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Define a\u00a0JobSchedule object variable by supplying the parent job \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'and name arguments in the constructor. \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschUserDBOptJobSched = New JobSchedule(jobUserDBOptJob, \"Sched 01\")\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Set properties to define the schedule frequency, and duration.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschUserDBOptJobSched.FrequencyTypes = FrequencyTypes.Weekly\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschUserDBOptJobSched.FrequencyRecurrenceFactor = 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschUserDBOptJobSched.FrequencySubDayTypes = FrequencySubDayTypes.Once\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim tsUserDBOptStart As TimeSpan\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0tsUserDBOptStart = New TimeSpan(3, 0, 0)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 jbschUserDBOptJobSched.ActiveStartTimeOfDay = tsUserDBOptStart\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim tsUserDBOptEnd As TimeSpan\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0tsUserDBOptEnd = New TimeSpan(23, 59, 59)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschUserDBOptJobSched.ActiveEndTimeOfDay = tsUserDBOptEnd\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschUserDBOptJobSched.FrequencyInterval = 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Dim dtUserDBOptStartDate As Date =\u00a0Date.Today\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschUserDBOptJobSched.ActiveStartDate = dtUserDBOptStartDate\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0'Create the job schedule on the instance of SQL Agent.\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0jbschUserDBOptJobSched.Create()<\/pre>\n<p>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&#8217;re done with the System Database Optimizations job.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">\u00a0\u00a0\u00a0\u00a0End Sub<\/pre>\n<p>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.\u00a0\u00a0Keeping your databases clean will help you get the maximum performance out of them and will keep your users (and your boss) happy.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 in SQL Server. &hellip;<\/p>\n","protected":false},"author":221822,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4249,4168,4336,4819,4150,4151,4194],"coauthors":[48342],"class_list":["post-305","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-backup-and-recovery","tag-database","tag-smo","tag-smo-vb-code-dba","tag-sql","tag-sql-server","tag-visual-basic"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/305","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221822"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=305"}],"version-history":[{"count":5,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/305\/revisions"}],"predecessor-version":[{"id":74826,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/305\/revisions\/74826"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=305"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=305"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=305"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=305"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}