the title will go into this...
TRANSCRIPT
Database Maintenance
Morris Caen
2
Agenda Explain Maintenance Components.
Create a Maintenance Plan using the SQL Studio Manager Wizard.
Create a Maintenance Plan using T-SQL.
Schedule a Maintenance Plan.
Maintenance Components
Maintenance Components• Check Database Integrity
• Shrink Database
• Reorganize Index
• Rebuild Index
• Update Statistics
Create a Maintenance Plan using the Wizard
Create a Maintenance Plan using the Wizard
6
• Open SQL Studio Manager
• Connect to your SQL Server
• Expand Management
• Right Click Maintenance Plans
• Select Maintenance Plan Wizard
Create a Maintenance Plan using the Wizard
• Click Next
7
Create a Maintenance Plan using the Wizard
• Enter a Name for the Maintenance Plan
• Click Change to create a Schedule
8
Create a Maintenance Plan using the Wizard
• Select Recurring and Enabled
• Occurs Weekly
• Assign a Time inside of the Maintenance Window defined in Radiant Management Studio
• Click OK
9
Create a Maintenance Plan using the Wizard
• Click Next
10
Create a Maintenance Plan using the Wizard
• Select the Tasks for Maintenance
• Select Check Database Integrity
• Select Shrink Database
• Select Rebuild Index
• Select Update Statistics
• Select Clean Up History
• Select Maintenance Cleanup Task
11
Create a Maintenance Plan using the Wizard
• Review Tasks
• Click Next
12
Create a Maintenance Plan using the Wizard
• Select Specific Databases, then select the CounterPoint database
• Click Next
13
Create a Maintenance Plan using the Wizard
• Select Specific Databases, then select the CounterPoint database
• Depending on the size of the database, increate the Shrink database when it grows beyond field.
• Click Next
14
Create a Maintenance Plan using the Wizard
• Select Specific Databases, then select the CounterPoint database
• Click Next
15
Create a Maintenance Plan using the Wizard
• Select Specific Databases, then select the CounterPoint database
• Click Next
16
Create a Maintenance Plan using the Wizard
• Click Next
17
Create a Maintenance Plan using the Wizard
• Click Next
18
Create a Maintenance Plan using the Wizard
• Click Next
19
Create a Maintenance Plan Using T-SQL
21
Create a Maintenance Plan Using T-SQL
• Database Integrity Check
– dbcc checkdb('DEMOAPRL')
22
Create a Maintenance Plan Using T-SQL
• Shrink Database
– dbcc shrinkdatabase (DEMOAPRL,5)
23
Create a Maintenance Plan Using T-SQL
• Rebuild Indexes– DBCC DBREINDEX(IM_ITEM,' ',90)– Rebuilding Indexes only runs on one table at a time so a cursor is generally
used to rebuild all indexes
DECLARE @TableName varchar(255)DECLARE @TableSchema varchar(255)
DECLARE TableCursor CURSOR FORSELECT table_name, table_schema FROM information_schema.tablesWHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName, @TableSchemaWHILE @@FETCH_STATUS = 0BEGINset @TableName=@TableSchema+'.'+@TableNameSET ARITHABORT ONDBCC DBREINDEX(@TableName,' ',90)FETCH NEXT FROM TableCursor INTO @TableName, @TableSchemaEND
CLOSE TableCursor
DEALLOCATE TableCursor
24
Create a Maintenance Plan Using T-SQL
• Update Statistics
– exec sp_updatestats
25
Create a Maintenance Plan Using T-SQL
• One Script:DECLARE @dbname varchar(100),
@SQL1 varchar(2500),
@CRLF varchar(2)
SET @dbname='DEMOAPRL'
SET @CRLF = Char(13) + Char(10)
set @sql1 =
'use ' + @dbname + @CRLF
set @SQL1 = @SQL1 + 'dbcc checkdb(''' + @dbname + ''')'
exec (@sql1)
set @sql1 =
'use ' + @dbname + @CRLF
set @SQL1 = @SQL1 + 'dbcc shrinkdatabase (' + @dbname + ',5)'
exec (@sql1)
set @sql1 =
'use ' + @dbname + @CRLF
exec (@sql1)
set @SQL1=
'DECLARE @TableName varchar(255)
DECLARE @TableSchema varchar(255)
DECLARE @dbname1 varchar(50)
DECLARE TableCursor CURSOR FOR
SELECT table_name, table_schema FROM ' + @dbname + '.information_schema.tables
WHERE table_type = ''base table'‘
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName, @TableSchema
WHILE @@FETCH_STATUS = 0
BEGIN
set @dbname1=''DEMOAPRL''
set @TableName=@dbname1+''.''+@TableSchema+''.''+@TableName
SET ARITHABORT ON
DBCC DBREINDEX(@TableName,'' '',90)
FETCH NEXT FROM TableCursor INTO @TableName, @TableSchema
END
CLOSE TableCursor
DEALLOCATE TableCursor'
exec (@SQL1)
set @sql1 =
'use ' + @dbname + @CRLF
set @SQL1 = @SQL1 + 'exec sp_updatestats'
exec (@sql1)
Creating a Schedule Using Studio Manager for a T-SQL Script
Creating a Schedule Using Studio Manager for a T-SQL Script
• Under SQL Server Agent, Right Click on Jobs
• Select New Job
27
• Assign a Name to the Job
• Make sure it is Enabled
28
Creating a Schedule Using Studio Manager for a T-SQL Script
• Click Steps
• Select New
29
Creating a Schedule Using Studio Manager for a T-SQL Script
• Give a Name to the Step
• Select the CounterPoint Database
• Open or Paste your Maintenance Script into the Window
• Click OK
30
Creating a Schedule Using Studio Manager for a T-SQL Script
• Select Schedules
31
Creating a Schedule Using Studio Manager for a T-SQL Script
• Click New
32
Creating a Schedule Using Studio Manager for a T-SQL Script
• Select Recurring
• Occurs Weekly
• Set Occurs once at: to a time after the database backup completes and inside the CounterPoint Maintenance Window defined in Radiant Management Console.
• Click OK
33
Creating a Schedule Using Studio Manager for a T-SQL Script
• Select Notifications
34
Creating a Schedule Using Studio Manager for a T-SQL Script
• Enable desired notifications for the system administrator.
• Click OK
35
Creating a Schedule Using Studio Manager for a T-SQL Script
Creating a Schedule Using Windows Task Scheduler for a T-SQL Script
Creating a Schedule Using Windows Task Scheduler for a T-SQL Script
• Create a Batch File or VBScript to call a maintenance script through OSQL.
• Sample Batch File
@ECHO OFF
OSQL –S localhost –Usa –PCounterPoint8 –i c:\maintenance.sql –h –o c:\maintenance.log
37
• Open Control Panel/Scheduled Tasks
• Select Add Scheduled Task
• Click Next
38
Creating a Schedule Using Windows Task Scheduler for a T-SQL Script
• Select Browse
39
Creating a Schedule Using Windows Task Scheduler for a T-SQL Script
• Select the Maintenance script
• Click Open
40
Creating a Schedule Using Windows Task Scheduler for a T-SQL Script
• Select Weekly
• Click Next
41
Creating a Schedule Using Windows Task Scheduler for a T-SQL Script
• Set Start Time to a time after the database backup completes and inside the CounterPoint Maintenance Window defined in Radiant Management Console.
• Select the day of the week to run Maintenance
• Click Next
42
Creating a Schedule Using Windows Task Scheduler for a T-SQL Script
• Enter user name and password for the Scheduled Task
• Click Next
43
Creating a Schedule Using Windows Task Scheduler for a T-SQL Script
• Click Finish
44
Creating a Schedule Using Windows Task Scheduler for a T-SQL Script
Q and A