Practical Maintenance Plans in SQL Server: Automation for the DBA

288 Pages·2016·22.244 MB·English

by  Bradley Beard

THE EXPERT’S VOICE® IN SQL SERVER
Practical
Maintenance
Plans in SQL
Server
Automation for the DBA
—
Bradley Beard
www.it-ebooks.info
Practical Maintenance
Plans in SQL Server
Automation for the DBA

Bradley Beard

www.it-ebooks.info
Practical Maintenance Plans in SQL Server
Bradley Beard
Palm Bay
Florida, USA
ISBN-13 (pbk): 978-1-4842-1894-5 ISBN-13 (electronic): 978-1-4842-1895-2
DOI 10.1007/978-1-4842-1895-2
Library of Congress Control Number: 2016938532
Copyright © 2016 by Bradley Beard
This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is
concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction
on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation,
computer software, or by similar or dissimilar methodology now known or hereafter developed. Exempted from this
legal reservation are brief excerpts in connection with reviews or scholarly analysis or material supplied specifically
for the purpose of being entered and executed on a computer system, for exclusive use by the purchaser of the work.
Duplication of this publication or parts thereof is permitted only under the provisions of the Copyright Law of the
Publisher’s location, in its current version, and permission for use must always be obtained from Springer. Permissions
for use may be obtained through RightsLink at the Copyright Clearance Center. Violations are liable to prosecution
under the respective Copyright Law.
Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to
the benefit of the trademark owner, with no intention of infringement of the trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as
such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights.
While the advice and information in this book are believed to be true and accurate at the date of publication, neither the
authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made.
The publisher makes no warranty, express or implied, with respect to the material contained herein.
Managing Director: Welmoed Spahr
Lead Editor: Jonathan Gennick
Development Editor: Douglas Pundick
Technical Reviewer: Mike McQuillan
Editorial Board: Steve Anglin, Pramila Balen, Louise Corrigan, Jim DeWolf, Jonathan Gennick, Robert Hutchinson,
Celestin Suresh John, Michelle Lowman, James Markham, Susan McDermott, Matthew Moodie, Jeffrey Pepper,
Douglas Pundick, Ben Renow-Clarke, Gwenan Spearing
Coordinating Editor: Jill Balzano
Copy Editor: Kim Burton-Weisman
Compositor: SPi Global
Indexer: SPi Global
Artist: SPi Global
Cover Designer: Anna Ishchenko
Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor,
New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail [email protected] , or visit
w ww.springer.com . Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business
Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation.
For information on translations, please e-mail [email protected] , or visit w ww.apress.com .
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions
and licenses are also available for most titles. For more information, reference our Special Bulk Sales–eBook Licensing web
page at w ww.apress.com/bulk-sales .
Any source code or other supplementary material referenced by the author in this text is available to readers at
www.apress.com . For detailed information about how to locate your book’s source code, go to
www.apress.com/source-code/ .
Printed on acid-free paper
www.it-ebooks.info
For you, my dearest Jessica
www.it-ebooks.info
www.it-ebooks.info
Contents at a Glance
About the Author ...................................................................................................xiii
About the Technical Reviewer .................................................................................xv
Acknowledgments .................................................................................................xvii
Introduction ............................................................................................................xix
■ Chapter 1: Introduction to Maintenance Plans ......................................................1
■ Chapter 2: Backing Up a Database ........................................................................7
■ Chapter 3: Checking Database Integrity ..............................................................33
■ Chapter 4: Executing SQL Server Agent Jobs ......................................................45
■ Chapter 5: Cleaning Up SQL Server Agent History ...............................................93
■ Chapter 6: Cleaning Up Maintenance Files ........................................................107
■ Chapter 7: Rebuilding Indexes ...........................................................................131
■ Chapter 8: Reorganizing Indexes .......................................................................147
■ Chapter 9: Shrinking the Database and Files ....................................................155
■ Chapter 10: Updating Object Statistics ..............................................................165
■ Chapter 11: Executing T-SQL Statements ..........................................................175
■ Chapter 12: Notifying Database Operators ........................................................189
■ Chapter 13: Tying It All Together .......................................................................205
Index .....................................................................................................................269
v
www.it-ebooks.info
www.it-ebooks.info
Contents
About the Author ...................................................................................................xiii
About the Technical Reviewer .................................................................................xv
Acknowledgments .................................................................................................xvii
Introduction ............................................................................................................xix
■ Chapter 1: Introduction to Maintenance Plans ......................................................1
Before We Start ................................................................................................................1
What Is a Maintenance Plan? ...........................................................................................2
Maintenance Plan Wizard Task Options ............................................................................3
Maintenance Plan Design Surface Options ......................................................................4
Summary ..........................................................................................................................6
■ Chapter 2: Backing Up a Database ........................................................................7
Recovery Models ..............................................................................................................7
Full ..........................................................................................................................................................9
Bulk Logged ............................................................................................................................................9
Simple.....................................................................................................................................................9
Backup Types ...................................................................................................................9
Full Backups .........................................................................................................................................10
Differential Backups .............................................................................................................................10
Transaction Log Backups ......................................................................................................................10
Setting Up the Maintenance Plan ...................................................................................12
Full Backup Confi guration ....................................................................................................................12
Differential Backup Confi guration .........................................................................................................18
vii
www.it-ebooks.info
■ CONTENTS
Transaction Log Backup Confi guration .................................................................................................21
Confi guring the Jobs ............................................................................................................................25
Summary ........................................................................................................................31
■ Chapter 3: Checking Database Integrity ..............................................................33
What Is Database Integrity? ...........................................................................................33
Practical Application of Database Integrity Principles ....................................................34
Setting Up the Maintenance Plan ...................................................................................36
Summary ........................................................................................................................43
■ Chapter 4: Executing SQL Server Agent Jobs ......................................................45
E-mail from the Database ..............................................................................................45
Confi guring E-mail ................................................................................................................................46
Sending a Test E-mail ...........................................................................................................................56
Enabling the Mail Profi le .......................................................................................................................57
SQL Agent Job Creation ..................................................................................................59
Creating an Example Table ...................................................................................................................60
Writing a Query for the Job...................................................................................................................60
Creating a SQL Agent Job .....................................................................................................................61
SQL Server Agent Options ....................................................................................................................62
Steps Tab ..............................................................................................................................................63
Schedules Tab .......................................................................................................................................68
Alerts Tab ..............................................................................................................................................70
Notifi cations .........................................................................................................................................76
Targets ..................................................................................................................................................79
Gmail’s SMTP .................................................................................................................80
Setting Up a Profi le ...............................................................................................................................80
Testing E-mail Confi guration ................................................................................................................88
Allowing Access to Google’s SMTP Server ...........................................................................................89
Enabling POP E-mail .............................................................................................................................90
Summary ........................................................................................................................91
viii
www.it-ebooks.info
■ CONTENTS
■ Chapter 5: Cleaning Up SQL Server Agent History ...............................................93
Setting Up the Maintenance Plan ...................................................................................93
Choosing Tasks ...............................................................................................................96
Defi ne What to Clean ....................................................................................................100
Review ..........................................................................................................................102
Summary ......................................................................................................................105
■ Chapter 6: Cleaning Up Maintenance Files ........................................................107
Separation of History from Maintenance .....................................................................107
Setting Up the Maintenance Plan .................................................................................108
Backups Cleanup ..........................................................................................................108
Deleting Backup Files .........................................................................................................................113
Deleting a Specifi c File .......................................................................................................................113
Searching and Deleting Based on Extension ......................................................................................113
Logs Cleanup ................................................................................................................117
Text Files Cleanup ........................................................................................................124
Summary ......................................................................................................................130
■ Chapter 7: Rebuilding Indexes ...........................................................................131
Indexes Explained ........................................................................................................131
Beginning Indexes ..............................................................................................................................132
B-Tree Structures ...............................................................................................................................136
Rebuilding vs. Reorganizing .........................................................................................137
Setting Up the Maintenance Plan .................................................................................137
Summary ......................................................................................................................146
■ Chapter 8: Reorganizing Indexes .......................................................................147
Reorganizing vs. Rebuilding .........................................................................................147
Setting Up the Maintenance Plan .................................................................................148
Summary ......................................................................................................................154
ix
www.it-ebooks.info