Practical Maintenance Plans in SQL Server Automation for the DBA

✍ Scribed by Beard, Bradley

• Publisher: Apress
• Year: 2016
• Tongue: English
• Leaves: 288
• Edition: 1st ed. 2016
• Category: Library

✦ Synopsis

This book is a complete guide to setting up and maintaining maintenance plans for SQL Server Database Administrators. Maintenance plans too often consist of a backup task and that's it, but there is so much more that can and must be done to ensure the integrity of your most important company resource - the data you are tasked to manage and safeguard. This book walks even the newest of users through creating a powerful, automated maintenance plan.

Automate your job using SQL Server Agent to leverage the power of Maintenance Plans to deliver real, proactive solutions to common issues. Schedule common tasks such as backups and index rebuilds to run automatically, and get early-warning notifications of impending problems relating to resource usage and query performance. By the time your boss knows to call you about a problem, you'll have already called him to describe your solution. The large majority of books never really cover the topic of inheriting a database server with multiple live databases; the common thread is that the databases will be created and maintained by the reader forever and ever. In the real world, that scenario rarely happens.Practical Maintenance Plans in SQL Servercovers that scenario and provides you with the knowledge and tools needed to get comfortable writing your own maintenance plans for any SQL Server database, whether created by you or inherited.


Shows the different tasks that can be run in a maintenance plan.
Explains how and why those tasks can be implemented.
Provides a roadmap to creating your own custom maintenance plan.

What You Will Learn
Implement a completely automated backup maintenance plan


Be alerted to performance problems and outages ahead of your boss


Learn the different types of database maintenance tasks
Plan the workflow of tasks within a maintenance plan
Automate your work by implementing custom maintenance plans
Who This Book Is For
Practical Maintenance Plans in SQL Serveris for any level of database administrator, but specifically it's for those administrators with a real need to set up a powerful maintenance plan quickly. New and seasoned administrators will appreciate the book for its robust learning pattern of visual aids in combination with explanations and scenarios. Practical Maintenance Plans in SQL Serveris the perfect "new hire" gift for new database administrators in any organization.

✦ Table of Contents

Contents at a Glance......Page 6
Contents......Page 8
About the Author......Page 14
About the Technical Reviewer......Page 16
Acknowledgments......Page 18
Introduction......Page 20
Before We Start......Page 22
What Is a Maintenance Plan?......Page 23
Maintenance Plan Wizard Task Options......Page 24
Maintenance Plan Design Surface Options......Page 25
Summary......Page 27
Recovery Models......Page 28
Backup Types......Page 30
Transaction Log Backups......Page 31
Full Backup Configuration......Page 33
Differential Backup Configuration......Page 39
Transaction Log Backup Configuration......Page 42
Configuring the Jobs......Page 46
Summary......Page 52
What Is Database Integrity?......Page 53
Practical Application of Database Integrity Principles......Page 54
Setting Up the Maintenance Plan......Page 56
Summary......Page 63
E-mail from the Database......Page 64
Configuring E-mail......Page 65
Sending a Test E-mail......Page 75
Enabling the Mail Profile......Page 76
SQL Agent Job Creation......Page 78
Writing a Query for the Job......Page 79
Creating a SQL Agent Job......Page 80
SQL Server Agent Options......Page 81
General......Page 82
Advanced......Page 84
Schedules Tab......Page 87
General......Page 89
Response......Page 90
Notifications......Page 95
Targets......Page 98
Setting Up a Profile......Page 99
Testing E-mail Configuration......Page 107
Allowing Access to Google’s SMTP Server......Page 108
Enabling POP E-mail......Page 109
Summary......Page 110
Setting Up the Maintenance Plan......Page 111
Choosing Tasks......Page 114
Define What to Clean......Page 118
Review......Page 120
Summary......Page 123
Separation of History from Maintenance......Page 124
Backups Cleanup......Page 125
Searching and Deleting Based on Extension......Page 130
Logs Cleanup......Page 134
Text Files Cleanup......Page 141
Summary......Page 147
Indexes Explained......Page 148
Beginning Indexes......Page 149
B-Tree Structures......Page 153
Setting Up the Maintenance Plan......Page 154
Summary......Page 163
Reorganizing vs. Rebuilding......Page 164
Setting Up the Maintenance Plan......Page 165
Summary......Page 171
Disk Usage Reporting......Page 172
Disk Space Considerations......Page 173
Setting Up the Maintenance Plan......Page 174
Summary......Page 181
Distribution Statistics Explained......Page 182
Setting Up the Maintenance Task......Page 183
Summary......Page 191
Setting Up the Maintenance Plan......Page 192
Implementing the Maintenance Plan......Page 197
Executing the Maintenance Plan......Page 200
Summary......Page 204
Setting Up the Maintenance Plan......Page 206
Creating an Operator Profile......Page 216
Summary......Page 220
Checking Your Environment......Page 221
Ordering of the Maintenance Tasks......Page 222
Determining Complexity of the Maintenance Plan......Page 223
Scenario 1......Page 225
Scenario 2......Page 226
Editing the Jobs......Page 229
Schedules Page Settings......Page 230
The sysjobschedules Table......Page 231
Targets Page Settings......Page 234
Saving the Changes......Page 235
Adding the Tasks to the Plan......Page 236
Adding the Check Integrity Task......Page 238
Adding the Rebuild Index Task......Page 240
Adding the Shrink Database Task......Page 241
Adding the Update Statistics Task......Page 242
Adding the Cleanup History Task......Page 244
Adding the bak Files Task......Page 245
Adding the txt Files Task......Page 247
Adding the trn Files Task......Page 249
Differential Backup Maintenance Activities......Page 251
Adding the Check Integrity Task......Page 252
Adding the Reorganize Index Task......Page 254
Adding the Shrink Database Task......Page 255
Adding the Update Statistics Task......Page 256
Adding the Check Integrity Task......Page 258
Adding the Reorganize Index Task......Page 260
Adding the Shrink Database Task......Page 261
Adding the Update Statistics Task......Page 263
Precedence Constraints......Page 264
Constraint Options......Page 266
Multiple Constraints......Page 267
Testing the Maintenance Plan......Page 271
Starting a Job from a T-SQL Script......Page 272
Summary......Page 283
Index......Page 285