Ad-Hoc Maintenance Plans

for Beginners

Tobiasz Janusz Koprowski

SQL Server MVP, FORG+

Слава Україні

Sponsors

ABOUT ME

Member of the Boards:

Polish Information Processing Society

Global IT Community Association

Polish SQL Server User Group Leader

Microsoft Certified Trainer (MCP, MCSA, MLSS, MLSBS, MCTS, MCITP)

SQL Server MVP (five years in a row)

MVP Mentor Program Member

Blogger, Influencer, Technical Writer

Last 9 years spend in Data Center in Wrocław

About15 years in IT/banking area

Speaker at SQL Server Community Launch, Time for SharePoint, CodeCamps,

SharePoint Community Launch, CISSP Day, SQL in the City, InfoTRAMS, SQL

Bits, SQL Saturday, CareerCon, Sharepoint & SQL Connection, IT Camp, SQL

Rally, SQL Relay…

Deep Dives Co-Author:

High availability of SQL Server in the context of SLA (Chapter 18th)

Technical reviewer:

Exploring MDX for SQL Server 2012

@KoprowskiT

AGENDA

Maintenance Plans – what are you talking about?

Maintenance Plans – how, where, when?

Methods, tools, right (?) ways

What Maintenance Plans can do you You

Back Up Database

Verify Integrity of Database

Maintain a Database Indexes

Maintain Index & Column Statistics

Remove Older Data from msdb

Remove Old Backups

Maintenance Plans – how control changes?

Examples

Q&A

#SQLSatKharkov 5

Maitenance Plans

– what we are talking about?

Maintenance Plans – what are you talking about?

Clean Environment

Silence

Knowledge about environment

Productivity / Performance

DEFINITION:

A database maintenance plan is a set of specific, proactive task that need to be

performer regularly on databases to ensure their adequate performance and

availability

#SQLSatKharkov 7

Maintenance Plans

– how, where, when?

Maintenance Plans – how, where, when?

HOW:

Maintenance Plan Wizard

Maintenance Plan Designer

T-SQL Scripts

PowerShell Scripts

WHERE

SQL Server Management Studio from Standard and above

See you in… Express

WHEN:

BEFORE: problems / service windows

AFTER: maintaining / clients back to home

#SQLSatKharkov 9

HOW: Maintenance Plan Wizard

Very simple (although advanced) wizard

Providing possibility for creation simple task in dozens of seconds

Enough in many cases, environments

Limited but huge number of options

Limitations:

number of databases

granularity

single tasks / no multitasking

no scripting for another instances

some known problems in previous version of SQL Server

SQL Server 2005 Service Pack 2

SQL Server 2008

#SQLSatKharkov 1

0

HOW: Maintenance Plan Designer

More flexible and advanced tool (not on this session)

Graphical user interface with SSIS

Most often used for „really DBA”

Increased features

Workflow for specified tasks with execution plan

Multitasking

Two additional Maintenance plans

Execute T-SQL Statement

Notify Operator

#SQLSatKharkov 1

1

LIMITATIONS: Maintenance Plan wizard & Designer

Limitations for both tools / those ideas are not possible

Identification and removing fragmentation for physical files

Identification abandoned, duplicated, forgotten indexes

Providing backup on demand

Werification good quality of backup

Werification succesful restore

Monitoring of performance

Monitoring of SQL OS

Monitoring of Windows OS

Monitoring available space

#SQLSatKharkov 1

2

Maintenance Plan: t-sql & powershell scripts

Functionality for „Real *Tru* DBA”

Necessary for working with multiple databases, instances, server farms

Feauters for scripting:

Access to OS

Posibility of moving

Scripts sharing

One of the best examples and tools for free:

HTTP://OLA.HALLENGREN.COM/

#SQLSatKharkov 1

3

What Maintenance Plans can do you You

Maintenance plans: available tasks

Check Database Integrity

DBCC CHECKDB

Shrink Database

NEVER, NEVER, NEVER

Reorganize Index

ALTER INDEX , and next Update Statistics

Rebuild Index

ALTER INDEX, Update Statistics are not necessary

Update Statistics

Sp_updatestats

History Cleanup

Using msdb, don’t do that too much frequently

#SQLSatKharkov 1

5

Maintenance plans: available tasks

Execute SQL Server Agent Job

Providing scrip using wen You work with agent

Back Up Database (Full)

Executing BACKUP DATABASE with FULL option, and next Transaction Log

Back Up Database (Differential)

Executing BACKUP DATABASE with DIFFERENTIAL option

Back Up Database (Transaction Log)

Executing BACKUP LOG, frequency...

Maintenance Cleanup Task

Most complicated feature ? BAK, TRN, TXT

#SQLSatKharkov 1

6

Functionality: Verify Integrity of Database

YOU SHOULD REMEMBER

using DBCC CHECKDB

Daily

Weekly

Monthly

DBCC CHECKDB (’database_name’) WITH NO_INFOMSGS

Suppresses all informational messages.

DBCC CHECKDB (’database_name’) WITH NO_INFOMSGS, ALL_ERRORMSGS Displays all reported errors per object. All error messages are displayed by default. Specifying or

omitting this option has no effect. Error messages are sorted by object ID, except for those

messages generated from tempdb database.

In SQL Server Management Studio, the maximum number of error messages returned is 1000.

DBCC CHECKDB (’database_name’) NOINDEX Specifies that intensive checks of nonclustered indexes for user tables should not be performed.

This decreases the overall execution time. NOINDEX does not affect system tables because

integrity checks are always performed on system table indexes

#SQLSatKharkov 1

7

POSSIBILITIES: SHRINK DATABASE

YOU SHOULD REMEMBER

NEVER USE THIS OPTION IN MAINTENACE PLANS

MDF & LDF

100 GB reserved

15 GB in use

DBCC SHRINKDATABASE

or

DBCC SHRINKFILE | MANUAL MODE

Reduction of Reserved Space

#SQLSatKharkov 1

8

1

8

POSSIBILITIES: Maintain Index & Column Statistics

YOU SHOULD REMEMBER

Automation:

AUTO_CREATE_STATISTICS

AUTO_UPDATE_STATISTICS

Executing automatically after: INSERT, UPDATE, DELETE

UPDATE STATISTICS table_name WITH FULLSCAN

When:

Never…

Just after Index Reorganization

On the days when we’re not using Index Rebuild or Reorganize Index

Important options (UPDATE STATISTICS):

All existing statistics

Column statistics only

Index statistics only

#SQLSatKharkov 1

9

POSSIBILITIES: Reorganize vs rebuild

POSSIBILITIES: history clean up (MSDB)

YOU MUST REMEMBER

Choosing the data (right data) for deleting:

Backup and restore history

SQL Server Agent job history

Maintenance Plan history

#SQLSatKharkov 2

1

Maitenance Plans

– how contol changes?

Link database to SVN, TFS, Git, Mercurial, Vault, Perforce

Source control for schemas and data

Push and pull database changes in SSMS

Check development history and access specific database versions

Store and share scripts to handle complex changes, such as column splits

Undo SQL Server changes

Exclude objects from source control using custom filters.

Supports SQL Server on Amazon RDS

View SQL differences between objects in a database and source control

Work with SQL Compare and SQL Data Compare to deploy databases directly from

source control

http://www.red-gate.com/products/sql-development/sql-source-control/

Maintenance plans: how contol it?

RedGate SQL Source Control with SSMS

RedGate SQL Source Control linked database

RedGate SQL Source Control

Summary

We should remember about:

Task sequences

Task execution

Task priority

Documentation

Using: daily / weekly / occasionally

Better expoloration of our environment

Monitoring (partially) of performance

Standard and above (sorry)

Maintenance Plan Wizard

for beginners and small installations

Maintenance Plan Designer

Adult , more complicated, wide environment

Maintenance Plans Control with RedGate Source Control

Scripts control

Database changes

SUMMARY

#SQLSatKharkov 2

8

Brad McGehee | Brad’ Sure Guide to SQL Server Maintenance Plans

„My new eBook, Brad’ Sure Guide to SQL Server Maintenance Plans is now available as

a free, 269 page PDF eBook. The book is designed for part-time or novice DBAs who

want to learn how to properly create Maintenance Plans using the tools that come with

SQL Server Management Studio (SSMS) in SQL Server 2005/2008.

LINK: http://bit.ly/1ijdnah

Ola Hallengreen | Maintenance Plans Scripts PASS slidedeck

„The SQL Server Maintenance Solution comprises scripts for running backups,

integrity checks, and index and statistics maintenance on all editions of Microsoft

SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012. The

solution is based on stored procedures, sqlcmd utility, and SQL Server Agent jobs”

LINK: http://bit.ly/1b220Ns

LINKS

#SQLSatKharkov 2

9

White Papers:

http://www.red-gate.com/products/sql-development/sql-source-

control/learn-more/white-papers

Case Study video of SQL Source Control & Continuous integration:

http://play.buto.tv/yhkvl

Product Manager& Developer session on Top tops for Continuous

Integration:

http://play.buto.tv/TzmXD

LINKS

#SQLSatKharkov 3

0

AFTER SESSION

CONTACT:

MAIL: KoprowskiT@windowslive.com

MSG: KoprowskiT@windowslive.com

TWITTER/FACEBOOK/LINKEDIN: KoprowskiT

SLIDES FROM SESSION:

SQLSat Page: http://www.sqlsaturday.com/290/schedule.aspx

SlideShare Profile: http://www.slideshare.net/Anorak

BLOGS:

ITPRO Anorak’s Vision: http://itblogs.pl/notbeautifulanymore/

Volume Licensing Specialites: http://koprowskit.eu/licensing/

My MVP Blog: http://koprowskit.eu/geek/

Sponsors

Дуже дякую!

Q & A

AND DON’T FORGOT ABOUT SURVEY

#SQLSatKharkov 3

3