SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

A time and a place for the SQL Server Maintenance Plan Wizard

By Brad McGehee,

In the course of my job, I get to give a lot of presentations, at various conference and user group events, many of them offering advice to DBAs on how to maintain their SQL Server databases. Aware of its limitations and failings I, like many other experienced DBAs, initially had a rather dismissive attitude towards use of the SQL Server Maintenance Plan Wizard for database maintenance. I advised my audience to avoid it and instead create their own custom maintenance scripts using T-SQL or PowerShell.

Of course, the great thing about giving presentations is that you don't just get to talk at people; you get to talk with them. Over the course of many post-seminar conversations, I began to realize that a significant proportion of the attendees were part-time, accidental, or novice DBAs, most of whom were using the Maintenance Plan Wizard. It dawned on me that, in effect, I was telling them that they were not doing their job correctly, and I began to question my stance on the tool.

It's certainly true that the Wizard has many significant limitations and, if used incorrectly, it can hurt SQL Server’s performance. However, it is equally true that a) if you know how to use it properly it will do a lot more good than harm and b) there are countless part-time and accidental DBAs out there who don't currently have the knowledge and experience to create their own custom scripts, and for whom finding the time and the training to acquire these skills is a slow process. For these DBAs, as they learn their trade, a simple tool that will help make sure essential database maintenance is carried out is an extremely attractive and viable option.

I thought I had been offering good advice but, by being so dismissive of the Maintenance Plan Wizard, I realized that I was effectively implying that "If you aren’t smart enough to create your own maintenance plans using T-SQL, then you shouldn’t be a DBA". It's a sentiment I hear echoed by many experienced DBAs; but that doesn't mean it's a very helpful one for the novice DBA. All DBAs start out as novices. Eventually, they become more experienced DBAs, but during the transition period, tools such as the Maintenance Plan Wizard, and its companion, the Maintenance Plan Designer, can still be used to good effect, even though they are less-than-perfect.

I decided to "put my money where my mouth is". I changed my recommendation to avoid the Wizard, created a new series of presentations on how to use it, and have just completed a book,  Brad's Sure Guide to Database Maintenance, advising DBAs how to get the most out of the Maintenance Plan Wizard and Designer, and get past some of its shortcomings. My goal is to help DBAs to maintain their databases as well as they possibly can, given their current level of knowledge, the time they have, and the needs of their database systems. Over time, then they can decide whether to continue using the Wizard and Designer, or start creating custom T-SQL or PowerShell scripts. Ultimately, it really doesn’t matter how a DBA implements optimal database maintenance, as long as the goal is achieved.

Brad M McGehee

Find out how you can get a free copy of the new e-book, Brad’s Sure Guide to SQL Server Database Maintenance here. I would also love to hear from any DBAS who would be willing to review the book, and offer feedback. If you're interested, you can contact me here: http://www.bradmcgehee.com/contact/

Total article views: 232 | Views in the last 30 days: 1
Related Articles

How Maintenance Plans are a Big Plus for SQL Server Administration

SQL Server 2012 makes your database maintenance jobs easy by proving an effective Maintenance Plan W...


The Maintenance Plan Wizard Can Lead You Astray

In theory, the SQL Server Maintenance Plan Wizard is supposed to make it easier for non-DBAs or novi...


How to Use SQL Backup Inside a Maintenance Plan

When you create a SQL Server Maintenance Plan using the Maintenance Plan Wizard or the Maintenance P...


Brad's Sure Guide to SQL Server Maintenance Plans

Brad's Sure Guide to Maintenance Plans shows you how to use the Maintenance Plan Wizard and Designer...