SQL Server has become more self-tuning and self-maintaining with every release. Yet, the need for basic maintenance such as removing index fragmentation, updating statistics, checking database consistency and taking backups will never go away. You can think of index maintenance much like an oil change for your vehicle and backups like an insurance policy. You can live without insurance, but it certainly comes in handy when you need it. Similarly your car can run without an oil change for a few thousand miles but frequent maintenance will produce better performance and will help your car last a few years longer.
SQL Server 2005 implements maintenance plans as SSIS packages allowing greater flexibility for customizing tasks. Plans now execute Transact-SQL commands you can review, instead of the undocumented stored procedures used in SQL Server 2000. Another area you can review is the execution history, which makes troubleshooting maintenance plans much less cumbersome.
[TABLE]
[TABLE]
Let's briefly recall what database maintenance plans look like with SQL Server 2000. You create a maintenance plan by clicking through a few wizard screens that let you choose options for complete backup, transaction log backup, index maintenance and checking database integrity. This sounds pretty easy, doesn't it? Yes, except the maintenance plan is a "blackbox;" The wizard doesn't show you the Transact-SQL statements executed by the jobs it creates.
Furthermore, if you have ever examined the properties of a job created through the maintenance plan wizard you know that job steps look similar to the following:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 7BCCF8E2-B8F4-4B92-BC3B-
F2FA1BAE25C0 -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB -UseDefDir -BkExt "BAK"'
In order to know what the maintenance plan does behind the scenes you must familiarize yourself with xp_sqlmaint extended stored procedure. This procedure in turn c
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
alls the SQLMAINT utility and has about a dozen switches. Alternatively you could also call SQLMAINT from the command line. Perhaps more importantly if the maintenance plan job ever fails the job history shows a completely useless piece of information similar to the following:
Executed as user: ServerName\UserName. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Fortunately you could navigate to the database maintenance plan's folder, right click the plan and choose "maintenance plan history" to get more descriptive errors. However, even this method of troubleshooting errors encountered by the maintenance plan jobs isn't always reliable.
In a nutshell SQL Server 2000 maintenance plan wizard makes creating important maintenance jobs very easy, but leaves much to be desired from the usability and supportability standpoint.
Much like other features maintenance plans have greatly improved with SQL Server 2005. Now maintenance plans are implemented as SQL Server Integration Services (SSIS) packages; This allows creating precedence constraints between jobs included with the maintenance plan. This news might intimidate you if you haven't used SSIS – but not to worry, you can still set up a maintenance plan by using a wizard within SQL Server Management Studio (SSMS). Building the maintenance plan within SSIS will provide additional flexibility but the wizard can help you get started and let you build a solid maintenance plan without your knowing a thing about SSIS as you will see below.
[TABLE]
To create a maintenance plan by using the wizard, navigate to the Management folder within SSMS, right-click the "Maintenance Plans" folder and choose Maintenance Plan Wizard. Note that SQL Server Agent service must be running while you're creating a database maintenance plan. The initial wizard screen allows you to choose the target server and specify the security credentials necessary for connecting to the server. Next you can choose the maintenance tasks you wish to perform, as shown on the screenshot below:
[IMAGE]
Screenshot 1: Choose maintenance tasks.
The screen gives you a brief overview of what you can accomplish with each type of maintenance task.
Next you can define the order of execution for the maintenance plan tasks. For example, you can rebuild indexes before checking the database integrity and running a full database backup. Then you can specify the target databases and objects to be affected by each task. For example, rebuild index task could affect a single database, multiple databases or all databases on a given instance. Furthermore, you're able to choose specific tables and indexed views on which you wish to rebuild indexes. You can also specify advanced options for rebuilding indexes such as the amount of free space you wish to leave available on index pages, whether you wish to pad the indexes and if you wish to keep the indexes online while they're being rebuilt. The screen for specifying index options is shown below:
[IMAGE]
Screenshot 2: Specify index options.
Next, define the schedule for executing your maintenance plan if desired. Alternatively you can run the maintenance plan jobs on demand. The next wizard screen allows you to specify a text file to which the status of the maintenance plan execution will be logged. You can specify the email address that should be notified with the status of the maintenance plan execution. The final screen provides a synopsis of the maintenance plan options you chose on previous screens. That's it! As I mentioned before you don't have to be an SSIS guru to create a database maintenance plan.
[TABLE]
Now let's see what actually happened during the building process. To view the SSIS package you just created right click on the maintenance plan and choose "modify". You will see a screen similar to the following:
[IMAGE]
Screenshot 3: View SSIS package.
Notice the flow of execution identified by the blue arrows; blue signifies that database integrity will be checked after completion of "rebuild index" task. The wizard only allows you to evaluate whether each task completes before executing the next task; so whether "rebuild index" task succeeds or fails the maintenance plan will continue with the task checking database integrity. But what if you want to conditionally execute different tasks depending on whether the previous task succeeds or fails? If you double-click the blue arrow, SSIS will allow you to edit the precedence constraint, as shown next:
[IMAGE]
Screenshot 4: Edit precedence constraints.
From this screen you can evaluate the result of each task's execution and invoke the remaining tasks based on success, failure or completion of the previous task. You can examine an expression in conjunction with the status of the current task before proceeding with other tasks. This is where the knowledge of SSIS could be particularly handy.
[TABLE]
Hopefully you like the improvements in the maintenance plan wizard and the SSIS package you can create by clicking through the wizard screens. But this is just a sneak preview of improvements!
SQL Server 2005 allows you to review the Transact-SQL command created by each step of the maintenance plan. SQLMAINT utility (along with xp_sqlmaint extended stored procedure) is deprecated and no longer used. Under the hood the maintenance plan is still implemented as a job but instead of calling xp_sqlmaint procedure, the job executes the SSIS package. Each job can be customized by passing parameter values and configuration files as well as execution and logging options as shown on the screenshot below.
[IMAGE]
Screenshot 5: Customize jobs.
I don't have the room to explain each tab of the maintenance job properties in this tip. You can learn more by reading SQL Server online documentation. But you can tell that SSIS allows great flexibility for customizing maintenance plan jobs.
SQL Server 2005 also allows you to examine the history of maintenance plan execution by right-clicking the maintenance plan and choosing "view history." Within the resulting dialog box you can see the history of each step of the maintenance plan:
[IMAGE]
Screenshot 6: View history of maintenance plan.
Clicking "View T-SQL" link on the bottom of the screen will show you the actual Transact-SQL command (or series of commands) executed by the job as shown next:
[IMAGE]
Screenshot 7: View T-SQL commands.
Summary
This tip demonstrates some of the welcome improvements with database maintenance plans in SQL Server 2005. Maintenance plans worked reasonably well in previous software versions, but were somewhat cryptic and difficult to troubleshoot. The new version implements maintenance plans as SSIS packages allowing greater flexibility for defining the sequence of the maintenance tasks, logging and configuration options. Furthermore, maintenance plan jobs no longer call a mysterious extended stored procedure with numerous switches. Instead they execute Transact-SQL commands you can review and feel comfortable with. Last but certainly not least, maintenance plans are considerably easier to troubleshoot due to the improved user interface for viewing their execution history.
[TABLE]
More on SearchSQLServer.com
Tip: Review SSIS benefits and commonly asked questions
Tip: Migrating SQL 2000 DTS packages to SSIS
Expert Advice: Handling metadata processes for SSIS projects
