SQL maintenance with Ola’s script

Steve Thompson (MVP) has an excellent blog describing how to implement SQL maintenance scripts for SCCM.  https://stevethompsonmvp.wordpress.com/2013/05/07/optimizing-configmgr-databases/

As you know, there is a a SCCM Site Maintenance job titled Rebuild Indexes.  Steve Thompson goes into some detail on his blog about why this doesn’t work…..at all!!

Enter:  Ola Hallengren SQL scripts:   https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Basically:

• Create a database and table to track the results of the indexing / statistics process

		-- USE [master]
		GO
		
		IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'CMMonitor')
		DROP DATABASE [CMMonitor]
		GO
		
		-- USE [master]
		GO
		
		-- CREATE DATABASE [CMMonitor] ON  PRIMARY 
		( NAME = N'CMMonitor', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\CMMonitor.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )
		 LOG ON 
		( NAME = N'CMMonitor_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\CMMonitor_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 5120KB )
		GO
		
		ALTER DATABASE [CMMonitor] SET COMPATIBILITY_LEVEL = 100
		GO
		
		IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
		begin
		EXEC [CMMonitor].[dbo].[sp_fulltext_database] @action = 'enable'
		end
		GO
		
		ALTER DATABASE [CMMonitor] SET ANSI_NULL_DEFAULT OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET ANSI_NULLS OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET ANSI_PADDING OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET ANSI_WARNINGS OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET ARITHABORT OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET AUTO_CLOSE OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET AUTO_CREATE_STATISTICS ON 
		GO
		
		ALTER DATABASE [CMMonitor] SET AUTO_SHRINK OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET AUTO_UPDATE_STATISTICS ON 
		GO
		
		ALTER DATABASE [CMMonitor] SET CURSOR_CLOSE_ON_COMMIT OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET CURSOR_DEFAULT  GLOBAL 
		GO
		
		ALTER DATABASE [CMMonitor] SET CONCAT_NULL_YIELDS_NULL OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET NUMERIC_ROUNDABORT OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET QUOTED_IDENTIFIER OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET RECURSIVE_TRIGGERS OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET  DISABLE_BROKER 
		GO
		
		ALTER DATABASE [CMMonitor] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET DATE_CORRELATION_OPTIMIZATION OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET TRUSTWORTHY OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET ALLOW_SNAPSHOT_ISOLATION OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET PARAMETERIZATION SIMPLE 
		GO
		
		ALTER DATABASE [CMMonitor] SET READ_COMMITTED_SNAPSHOT OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET HONOR_BROKER_PRIORITY OFF 
		GO
		
		ALTER DATABASE [CMMonitor] SET  READ_WRITE 
		GO
		
		ALTER DATABASE [CMMonitor] SET RECOVERY SIMPLE 
		GO
		
		ALTER DATABASE [CMMonitor] SET  MULTI_-- USER 
		GO
		
		ALTER DATABASE [CMMonitor] SET PAGE_VERIFY CHECKSUM  
		GO
		
		ALTER DATABASE [CMMonitor] SET DB_CHAINING OFF 
		GO

• Download the ‘MaintenanceSolution.sql’ script from here: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
  • Change the — USE [Master] line to — USE [CMMonitor]
• Optional:  Delete the following SQL Agent Jobs that were created using the above script
  • All Backup jobs
  • Output File Cleanup job
  • SP_Delete_backuphisorty job
  • Syspolicy_purge_history job
• Schedule the CommandLog Cleanup job
  • Weekly – Sunday – 21:00
• Schedule the DatabaseIntegrityCheck – System Databases job
  • Daily at 22:00
• Schedule the DatabaseIntegrityCheck – User databases job
  • Daily at 22:00
• Edit the IndexOptimize – User_Databases job
  • Rename the Step 1 to “IndexOptimize – CM_[SITE CODE] and SUSDB”
  • Modify the step to be a T-SQL type
  • Edit the comand:

EXECUTE dbo.IndexOptimize

@Databases = "CM_[SCCM SITE CODE], SUSDB",

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 10,

@FragmentationLevel2 = 40,

@UpdateStatistics = 'ALL',

@OnlyModifiedStatistics = 'Y',

--@SortInTempdb = 'Y',

@LogToTable = 'Y'

• Schedule the job
  • Daily at 23:30