4. Jobs
SQL Server Agent jobs make it
possible for you to perform routine scheduled maintenance on your SQL
Server instances. A job is made up of a series of steps or actions that
work together to perform a task. You can place each job in a category to
group tasks that are similar in nature. For example, you can use the
Database Maintenance category to group all of your maintenance jobs. You
can execute a job using a predefined schedule or by using the sp_start_job stored procedure in the msdb.
SQL Server Agent can notify an operator when a job fails, when a job
succeeds, or any time a job completes.
Right-click the Jobs folder
under SQL Server Agent and select New Job from the context menu to open
the New Job dialog box, as shown in Figure 6.
Give the job a descriptive name, such as Cleanup Mail History.
Select Database Maintenance from the Category drop-down list. If you do
not have a Database Maintenance category or you want to create a new
category, you can right-click the Jobs folder and select Manage Job
Categories from the context menu. Provide a brief description, such as Database maintenance job used to purge mail history older than 30 days from the msdb. If Enabled is unchecked, the job will not run during the scheduled time; however, you can still execute the job using the sp_start_job stored procedure. A disabled job will also run if it is executed in response to an alert. Select the Steps page, as shown in Figure 7, to define the steps the job will perform.
You can use the Steps page
to add, edit, and delete job steps, move the order of the steps, and set
the start step for the job. Select the New button to display the New
Job Step dialog box shown in Figure 8.
Give the job step a descriptive name, such as Delete Mail History. You can create job steps to execute many different actions, such as the following:
ActiveX scripts will be
removed from SQL Server Agent in a future version of SQL Server.
Therefore, you should avoid using ActiveX scripts in any new jobs.
|
|
Since we are executing a SQL
script, select Transact-SQL Script (T-SQL) from the Type drop-down list.
The Run As drop-down list in the General page does not apply to T-SQL
scripts; it only applies to other step types that interact with the OS,
such as PowerShell scripts and CmdExec. The Run As option here is for
designating a proxy account to run the job step. (We will discuss proxy
accounts more in the next section.) Actually, if you change Type
selection to something other than T-SQL, and then change it back to
T-SQL, the Run As option will be disabled.
Change the Database option to msdb,
since that is where the Database Mail cleanup stored procedures are
located. Enter the following script in the Command text box. Select the
Advanced page, as shown in Figure 9, to configure the remaining options for the job step.
DECLARE @BeforeDate DateTime =
(Select DATEADD(d,-30, GETDATE()))
EXEC sysmail_delete_mailitems_sp
@sent_before = @BeforeDate
EXEC sysmail_delete_log_sp
@logged_before = @BeforeDate
You can use the Advanced
page to define options for the job step, such as completion actions and
logging. You can use the On Success Action and On Failure Action lists
to quit the job reporting success, quit the job reporting failure, go to
the next step, or to select a specific step that you would like to run
next as a result of the action. You can use the Retry Attempts and Retry
Interval options to set the number of times SQL Server Agent will try
to run the steps and how long it will wait between retry attempts before
it considers the step a failure. We have set our job step to retry one
time 15 minutes after the initial failure. It is important to specify an
output file to log the messages returned by the job step. Output files
are a tremendous help when troubleshooting issues related to the job
failures. The Run As User option in the Advanced page is only applicable
for T-SQL scripts. If you create a step that is anything other than
T-SQL, the option will not be displayed. The account specified here is
the SQL Server account used by SQL Server Agent to execute the job step.
Only users with sysadmin rights can set the Run As User option and
create output files. Click OK to add the new step to the job.
You can use the Schedules page
of the New Job dialog to pick an existing schedule or create a new
schedule for the job. Click the Schedules page and select New to open
the New Job Schedule dialog box shown in Figure 10.
Give the new schedule a descriptive name, such as Midnight Every Sunday.
The options are pretty self-explanatory. Select the appropriate
schedule for the new job. You will see a Summary area at the bottom that
you can use to validate the choices you have made. As indicated by the
name, we have created a schedule to run every Sunday at 12:00 AM. Select
OK to add the job to the new schedule.
You can use the Alerts page
to create a new alert that will execute the job in response to an
event. Since we do not need to clean up Database Mail for an alert, skip
this page and select Notifications, as shown in Figure 11.
You can use the
Notifications page to specify the actions SQL Server Agent will perform
when the job completes. All of the drop-down lists on this page have the
same three options: When the Job Fails, When the Job Succeeds, and When
the Job Completes. You can use these options to notify an operator,
write to the Windows Application event log, and automatically delete the
job. In our case, we want to email the DBA Support operator when the job fails. Click OK to create the new Job.
You only need to worry about
the Targets page if you are using master and target servers within SQL
Server Agent. SQL Server Agent allows you to designate a SQL Server
instance as a master (MSX) server and push jobs to target (TSX) servers.
The Targets page allows you to define whether the job will be defined
locally or if you will be targeting multiple servers. The jobs on the
target servers are read-only and cannot be deleted. In order to set up a
MSX server and enlist TSX servers, you can right click on SQL Server
Agent, select Multi-Server Administration, and then select Make This a
Master. This will start the Master Sever Wizard that will guide you
through the process. For more information on master and target servers,
search for "Creating a Multiserver Environment" in SQL Server Books
Online.
5. Proxies
You can create a proxy
account that allows SQL Server Agent to execute a job step that runs
under the credentials of a specified Windows user account. You can use a
proxy account to access subsystems external to SQL Server using Windows
credentials; therefore, you cannot use a proxy account for a T-SQL job
step, since T-SQL is an internal operation. In order to create a proxy
account, you must first create a credential that maps to a Windows user
account. To open the New Credential dialog box shown in Figure 12,
expand the Security node in SQL Server Management Studio, right-click
the Credentials folder, and select New Credential from the context menu.
Enter a descriptive name for
the credential. We named ours Ken_Credential, since that is the Windows
account we are using. Enter a valid Windows user account in the Identity
field. You can select the ellipsis next to the Identity field to
display the Select User or Group dialog box, which will allow you to
search for and verify Windows user accounts. Enter the password for the
Windows user account and select OK to create the new credential. You may
also choose to verify the Windows user account by an Extensible Key
Management provider if you have one installed on the server.
Now that you have created a
credential, you are ready to create a new proxy. Expand SQL Server
Agent, right-click the Proxies folder, and select New Proxy from the
context menu. This will display the New Proxy Account dialog box shown
in Figure 13.
Give the new proxy account a
descriptive name; we called ours Ken_Proxy. Now, enter the name of the
credential created earlier in this section. You can also select the
ellipsis next to the Credential Name text box to search for the
credential. Enter a description for the new proxy account, such as Proxy used to execute Operating System and PowerShell scripts.
Select the subsystems the new proxy account will use. We have selected
Operating System (CmdExec) and PowerShell. Select the Principals page,
as shown in Figure 14, to manage the principals that can use the new proxy account in job steps.
Select the Add button to
assign SQL Server logins or roles to the proxy account. Members of the
sysadmin role have access to all proxy accounts, so you do not need to
add them on the Principals page. Click OK to create the new proxy
account.
The proxy account is now an
available option in the Run As drop-down list on the General page of the
New Job Step dialog box. The proxy account is only available in the Run
As list if you select Operating System (CmdExec) or PowerShell as the
step type, since they are the only two subsystems we allowed the proxy
account to use.