SQL Server Agent (11/12): Maintenance Scheduled jobs

SQL Server Agent is the core of all real-time databases. Proxies have a lot of non-obvious uses, so the knowledge of the system is useful for developers or DBAs. This series of articles will be popular to introduce its many uses.

In this series of previous articles, we looked at the work of completing a job step using a proxy account that mimics the Windows security context. Most subsystems support proxy accounts, while subsystems restrict proxy accounts, requiring users to explicitly authorize the use of proxy credentials in job steps. In this article, you will check the maintenance plan. A maintenance plan is used to perform various tasks to optimize your database, including backup, rebuild, and/or restructure indexes, and update statistics. The maintenance plan creates the job. In this article we will review the maintenance plan and view the jobs created by the maintenance plan.

Maintenance Plan Overview

A maintenance plan is a very nice graphical interface to help you maintain common tasks for SQL Server databases. Using a maintenance plan is a good shortcut, rather than manually creating the correct T-SQL or PowerShell scripts to perform these routine maintenance tasks. These tasks include:

• check Database integrity
• shrink database
• reorganize index
• rebuild index
• UPDATE statistics
• clear history
• Execute SQL Server Agent jobs
• backup database

You can literally understand what these tasks can do, but you can also learn more about https://msdn.microsoft.com/zh-cn/library/ms140255.aspx. If you look at the links you will notice that you are reading SQL Server integration Services (SSIS) tasks. This is because the maintenance plan is actually an SSIS package that is easy for DBA to create and maintain.
Each maintenance plan consists of one or more sub-plans. Each sub-plan can run on a different schedule (because they are separate jobs under SQL Server Agent). You can only logically associate a sub-plan in a maintenance plan. You can have multiple maintenance plans, and the maintenance plan can be applied to one or more databases.

Create a maintenance plan

First you will notice that the maintenance plan is not under SQL Server Agent. Navigate to the maintenance plan, managed by. The maintenance plan is not created by default on the database. Right-click the maintenance plan and you see the new Maintenance Plan and maintenance plan wizards, and most people find it quicker and easier to create a maintenance plan using wizards. Start it, and then you'll see the interface shown in illustration 1

Figure 1: Start the Maintenance Plan Wizard
Click Next to give the maintenance plan a name. For example, you intend to perform maintenance tasks on the AdventureWorks database, so you can name AdventureWorks maintenance. Type a description and select individual schedule for each task to view a complex agent job schedule. As illustrated in Figure 2:

Figure 2: Select Schedule Properties
Click Next, then select the maintenance task you want to perform, select "Check Database Integrity", "UPDATE STATISTICS", "Backup Database (Full)", as illustrated in Figure 3:

Figure 3: Selecting maintenance Tasks
Click Next to select the order in which the tasks are performed. Continue to the next step, you will see the Define Database Check integrity panel, select AdventureWorks from the list of databases, as illustrated in Figure 4. Note that you can select multiple databases, all databases, system databases, all user databases. You can also check "ignore databases that are not online" to avoid errors when you choose to maintain all databases.

Fig. 4: Selecting a Database
Click OK and click the Edit button at the bottom of the plan. You will see a standard Job scheduling dialog box, as shown in Figure 5. In this case, press the default settings.

Figure 5: Creating a scheduled schedule
Click OK and you will notice that the AdventureWorks database has been selected under a specific database and the schedule below is updated to weekly schedule. Click Next, you will see the "Update Statistics" task, the database list select AdventureWorks, the other default, and create the default job schedule.
Click Next to go to the "Backup Database (Full)" task, select the AdventureWorks database, other default (you can tick "verify backup integrity") and also create the default scheduled schedule (illustrated in Figure 6).

Illustration 6: Creating a Database backup task
Click Next, you will see a SELECT report option, default is saved to the database errorlog log folder, you can send a copy of the report to someone if you have configured Database Mail. Accept the default and click Next to complete the wizard. This wizard will run, create a maintenance plan, and after completion will have success information returned, as illustrated in Figure 7:

Fig. 7: Maintenance Plan Wizard Complete
If you want to see the results screen in the Maintenance Plan Wizard, you can refresh the maintenance Plan folder, and then double-click the newly created maintenance plan, as illustrated in Figure 8:

Figure 8: View Maintenance plan
Close the maintenance plan, and navigate to the SQL Server Agent job folder. Now you will see three new jobs, one for each sub-plan in the maintenance plan (as illustrated in Figure 9). You have three different sub-programs because you choose to have three separate plans.

Fig. 9: Maintenance Plan Jobs
Open the AdventureWorks maintenance.subplan_1 job, then click Steps, and then open step subplan_1. You will find that the step type is "SQL Server Integration Services Package" (as illustrated in Figure 10). If you are familiar with the skills of SSIS, you can manually edit the properties, but this is not recommended because you may interrupt the graphical maintenance plan to work properly; however, you can specify the proxy account and configure the connection information.

Fig. 10: Maintenance Plan Job steps
You can go back to the job and then change the schedule for the job. You may have found that the current example does not perform well in order because they are configured at the same time. Therefore, it makes more sense to choose a suitable plan.

View maintenance Plan jobs under job activity monitor

Each job created by a maintenance plan is placed in a special job category-Database maintenance. You can use the filter feature under the Job Activity Monitor. Open Job Activity Monitor, click Filter ... Button. "Category", type "Database Maintenance" (unfortunately you must type it, no drop-down menu to select an existing category). Tick apply filter, then click OK. You should now see only the maintenance scheduled job, which makes it easier to check the execution and results of your maintenance plan job (as illustrated in Figure 11).

Figure 11: Filtering maintenance plan jobs in Activity Monitor

Next Trailer

Maintaining a scheduled job is an easy way to maintain the daily management of the database. The essence of the maintenance plan is supported by the operation, the operation steps and the plan. You need to understand the nature of these jobs to make sure that you won't break or delete them.
In our last article, we'll look at the msx/tsx feature extension job management to multiple servers using SQL Server Agent.

Original address: http://www.sqlservercentral.com/articles/Stairway+Series/72462/

Reference article: http://www.cnblogs.com/Uest/p/4564674.html

SQL Server Agent (11/12): Maintenance Scheduled jobs