5 .com



Contents TOC \o "1-3" \h \z \u 5 - CMMonitorDB.sql PAGEREF _Toc353792826 \h 16 - MaintenanceSolution-Custom.sql PAGEREF _Toc353792827 \h 67 - Create Agent task.sql PAGEREF _Toc353792828 \h 75 - CMMonitorDB.sqlModify file location for database and log file as appropriate.USE [master]GO/****** Object: Database [CMMonitor] Script Date: 03/04/2013 11:30:08 ******/IF EXISTS (SELECT name FROM sys.databases WHERE name = N'CMMonitor')DROP DATABASE [CMMonitor]GOUSE [master]GO/****** Object: Database [CMMonitor] Script Date: 03/04/2013 11:30:08 ******/CREATE DATABASE [CMMonitor] ON PRIMARY ( NAME = N'CMMonitor', FILENAME = N'C:\MSSQL\DATA\CMMonitor.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ) LOG ON ( NAME = N'CMMonitor_log', FILENAME = N'C:\MSSQL\Log\CMMonitor_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 5120KB )GOALTER DATABASE [CMMonitor] SET COMPATIBILITY_LEVEL = 100GOIF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))beginEXEC [CMMonitor].[dbo].[sp_fulltext_database] @action = 'enable'endGOALTER DATABASE [CMMonitor] SET ANSI_NULL_DEFAULT OFF GOALTER DATABASE [CMMonitor] SET ANSI_NULLS OFF GOALTER DATABASE [CMMonitor] SET ANSI_PADDING OFF GOALTER DATABASE [CMMonitor] SET ANSI_WARNINGS OFF GOALTER DATABASE [CMMonitor] SET ARITHABORT OFF GOALTER DATABASE [CMMonitor] SET AUTO_CLOSE OFF GOALTER DATABASE [CMMonitor] SET AUTO_CREATE_STATISTICS ON GOALTER DATABASE [CMMonitor] SET AUTO_SHRINK OFF GOALTER DATABASE [CMMonitor] SET AUTO_UPDATE_STATISTICS ON GOALTER DATABASE [CMMonitor] SET CURSOR_CLOSE_ON_COMMIT OFF GOALTER DATABASE [CMMonitor] SET CURSOR_DEFAULT GLOBAL GOALTER DATABASE [CMMonitor] SET CONCAT_NULL_YIELDS_NULL OFF GOALTER DATABASE [CMMonitor] SET NUMERIC_ROUNDABORT OFF GOALTER DATABASE [CMMonitor] SET QUOTED_IDENTIFIER OFF GOALTER DATABASE [CMMonitor] SET RECURSIVE_TRIGGERS OFF GOALTER DATABASE [CMMonitor] SET DISABLE_BROKER GOALTER DATABASE [CMMonitor] SET AUTO_UPDATE_STATISTICS_ASYNC OFF GOALTER DATABASE [CMMonitor] SET DATE_CORRELATION_OPTIMIZATION OFF GOALTER DATABASE [CMMonitor] SET TRUSTWORTHY OFF GOALTER DATABASE [CMMonitor] SET ALLOW_SNAPSHOT_ISOLATION OFF GOALTER DATABASE [CMMonitor] SET PARAMETERIZATION SIMPLE GOALTER DATABASE [CMMonitor] SET READ_COMMITTED_SNAPSHOT OFF GOALTER DATABASE [CMMonitor] SET HONOR_BROKER_PRIORITY OFF GOALTER DATABASE [CMMonitor] SET READ_WRITE GOALTER DATABASE [CMMonitor] SET RECOVERY SIMPLE GOALTER DATABASE [CMMonitor] SET MULTI_USER GOALTER DATABASE [CMMonitor] SET PAGE_VERIFY CHECKSUM GOALTER DATABASE [CMMonitor] SET DB_CHAINING OFF GO6 - MaintenanceSolution-Custom.sqlObtain script from source below – make the change to write the maintenance to your database created in the first step!/*SQL Server Maintenance Solution - SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012Backup: Check: and Statistics Maintenance: solution is free: can contact me by e-mail at ola@.Last updated 2 March, 2013.Ola Hallengren*/-- Make this change! (Steve T)-- USE [master] -- Specify the database in which the objects will be created.USE [CMMonitor]7 - Create Agent task.sqlNote: as mentioned during the presentation, you’ll modify the database name below from ''CM_CAS'' to your database name. Also, you can have multiple databases specified by using: ''CM_CAS,SUSDB''USE [msdb]GO/****** Object: Job [IndexOptimize - AME] Script Date: 03/13/2013 14:36:00 ******/BEGIN TRANSACTIONDECLARE @ReturnCode INTSELECT @ReturnCode = 0/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 03/13/2013 14:36:00 ******/IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackENDDECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'IndexOptimize - MMS2013', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUTIF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback/****** Object: Step [Defragment Indexes] Script Date: 03/13/2013 14:36:02 ******/EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Defragment Indexes', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'EXECUTE dbo.IndexOptimize@Databases = ''CM_CAS'',@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''', @database_name=N'CMMonitor', @flags=0IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Execute Defragment Indexes', @enabled=1, @freq_type=8, @freq_interval=2, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20130124, @active_end_date=99991231, @active_start_time=180000, @active_end_time=235959, @schedule_uid=N'c172b0e8-0f61-48d9-981b-97222b5235f7'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackCOMMIT TRANSACTIONGOTO EndSaveQuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GO ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download