code 128 check digit c# Figure 9 Defining the maintenance cleanup task in Visual C#

Generation Code 128C in Visual C# Figure 9 Defining the maintenance cleanup task

You should think about the reporting options. The dialog box shown in figure 10 is not from the wizard, it is from the Designer. The Designer dialog box is a superset of the wizard dialog box, so you ll find all options in the wizard discussed here. The reason I decided to show this dialog box from the Designer is that it has some options that the wizard does not have. If something goes wrong (like a backup fails or DBCC CHECKDB detects database corruption), you definitely want to know about it as soon as possible and with as much information as possible so that you can take appropriate action without any unnecessary delay. I find a report file good to have, even if there is history for each execution of the plan available (right-click an existing plan and select View History). For most cases, I like to enable email support within SQL Server and configure the jobs to email the operator. The exception is for jobs that execute frequently. In most cases, you will hopefully also have some monitoring solution for errors in the Windows Event Log, various error log files, high-severity errors (such as 823, 824), and the like.

For some reason, this task, shown in figure 11, is not available from the wizard. If you want to add this task, finish the wizard and let it save the plan, and then right-click the

plan and select Modify, which will open the Designer. I find this task useful, because it allows us to add whatever T-SQL commands we wish to an MP. Not only can we set the order in which tasks run in the plan, we can execute other Agent jobs as well as T-SQL commands from within our MPs.

So now you have a maintenance plan. The Maintenance Plan Wizard or Designer, whichever you used, has created one or several Agent jobs for you. Make sure that the Agent is running and using an appropriate service account (the Windows account that is used for the SQL Server Agent service). The service account should exist as a Windows login in SQL Server, having sysadmin privileges. If you can start the Agent, you know you ve fulfilled this, because the Agent refuses to start unless it can log in to SQL Server with sysadmin privileges. Also, the account needs access to the right folders and shares and other resources for whatever you perform in the MP. When the plan has been executed, you might want to be notified. You can either set up notification of an Agent operator in the MP s Select Report Options dialog box, or you can specify notification at the job level. I prefer the former, because this allows me to include the information from the MP report file in the email, which means I can immediately start troubleshooting possible problems when I receive the email. You can also right-click an existing MP in SSMS and select View History, which will show the history information from MP history tables in msdb, such as sysmaintplan_log and sysmaintplan_logdetail.

Summary

Summary

MPs are powerful tools. They allow you to easily and quickly define various types of maintenance operations for your SQL Server instance without needing any T-SQL

programming. As you become more experienced and see a need for more tailored plans, you can refine your existing MPs. You can pick and choose between the options in the predefined tasks, and replace some of those with your own T-SQL or SQL Server Agent job tasks. Some DBAs prefer to skip the MPs altogether, and do everything using some combination of T-SQL code, scripting language, and a job scheduler (like SQL Server Agent). I ve done my share of that. What you should remember if you do this is that although it is extremely powerful and allows for ultimate flexibility (you write your own code, after all), the code you write needs to be written, debugged, tested, and maintained over time. I hesitate to leave home-grown maintenance jobs with customers who do not have the competency to maintain this code over time. In many cases, MPs provide a good middle ground, and with the added flexibility introduced in SQL Server 2005, they become even more powerful and versatile.