5 - web view3/2/2013 · sql server maintenance solution - sql server 2005, sql server...

12
Contents 5 - CMMonitorDB.sql.................................................1 6 - MaintenanceSolution-Custom.sql..................................6 7 - Create Agent task.sql...........................................7 5 - CMMonitorDB.sql Modify 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 )

Upload: vuongkhuong

Post on 30-Jan-2018

229 views

Category:

Documents


0 download

TRANSCRIPT

Page 1: 5 -    Web view3/2/2013 · SQL Server Maintenance Solution - SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012

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 )

Page 2: 5 -    Web view3/2/2013 · SQL Server Maintenance Solution - SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012

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

Page 3: 5 -    Web view3/2/2013 · SQL Server Maintenance Solution - SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012

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

Page 4: 5 -    Web view3/2/2013 · SQL Server Maintenance Solution - SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012

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

Page 5: 5 -    Web view3/2/2013 · SQL Server Maintenance Solution - SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012

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

Page 6: 5 -    Web view3/2/2013 · SQL Server Maintenance Solution - SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012

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]

Page 7: 5 -    Web view3/2/2013 · SQL Server Maintenance Solution - SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012

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,

Page 8: 5 -    Web view3/2/2013 · SQL Server Maintenance Solution - SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012

@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'',

Page 9: 5 -    Web view3/2/2013 · SQL Server Maintenance Solution - SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012

--@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

Page 10: 5 -    Web view3/2/2013 · SQL Server Maintenance Solution - SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO