5 - web view3/2/2013 · sql server maintenance solution - sql server 2005, sql server...
TRANSCRIPT
Contents5 - CMMonitorDB.sql...............................................................................................................................1
6 - MaintenanceSolution-Custom.sql......................................................................................................6
7 - Create Agent task.sql..........................................................................................................................7
5 - 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]
GO
USE [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 )
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
6 - 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 2012
Backup: http://ola.hallengren.com/sql-server-backup.html
Integrity Check: http://ola.hallengren.com/sql-server-integrity-check.html
Index and Statistics Maintenance: http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
The solution is free: http://ola.hallengren.com/license.html
You can contact me by e-mail at [email protected].
Last updated 2 March, 2013.
Ola Hallengren
http://ola.hallengren.com
*/
-- 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 TRANSACTION
DECLARE @ReturnCode INT
SELECT @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)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @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 OUTPUT
IF (@@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=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @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 QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO