When you automate routine maintenance tasks, you typically use SQL Server Enterprise Manager to create operators and jobs. You can also use the Create Job Wizard, execute system stored procedures, or write your own administrative application using SQL-DMO. This lesson describes how to create operators, create and schedule jobs, and configure SQL Mail to send and receive e-mail messages.
After this lesson, you will be able to
- Create operators to notify when a job completes or when an alert is fired
- Understand the different types of job steps supported by SQL Server
- Create and schedule jobs
- Configure SQL Mail to send and receive e-mail messages
Estimated lesson time: 90 minutes
When a job completes, you have several notification options. You can write to the Windows NT application log, delete the job, or notify an operator by pager, e-mail, or a net send command.
To define new operators, you can use SQL Server Enterprise Manager or execute the sp_add_operator system stored procedure. The operator definitions are stored in the msdb..sysoperators system table. When you create operators, consider the following facts and guidelines:
For each operator, the date and time of the most recent attempts to send each type of notification (e-mail, pager, and net send command) are logged.
If an operator is not receiving notifications, you should
In this exercise, you will create an operator, send a message, and verify that the message was received.
TIP
If the message has not yet arrived, click Deliver Now on the Tools menu to check for new messages. Do this to check for new messages immediately whenever you are expecting a new message; otherwise, the message will take a little while to arrive.
To define a new job, you can use SQL Server Enterprise Manager or execute the sp_add_job system stored procedure. You can also use the Create Job Wizard. The job definition is stored in the msdb..sysjobs system table. This table is maintained in cache to improve performance. When you define jobs, you should
In this exercise, you will use the Create Job Wizard to define a job that backs up the StudyNwind transaction log every day at 5:00 p.m.
Option | Value |
---|---|
Job command type | Transact-SQL command |
Database name | StudyNwind |
Transact-SQL statement | BACKUP LOG StudyNwind TO DISK = 'C:\Mssql7\Backup\Nwindlog.bak' |
Schedule | On a recurring basis. Daily, Every 1 day, Occurs once at 5:00 P.M. |
Notifications | E-mail Admin and Net send Admin |
Job name | StudyNwind Log Backup |
In this exercise, you will verify that your job was created successfully and that notifications were sent.
Note that a message appeared on your screen from a net send command.
What information is displayed in the job history when Show Step Details is selected? What information is displayed when this option is cleared?
Answer
When you create a job, you must define the individual steps that make up that job. You can use SQL Server Enterprise Manager or execute the sp_add_jobstep system stored procedure to define each job step. The job step definitions are stored in the msdb..sysjobsteps system table.
Job steps can be Transact-SQL statements, replication tasks, operating system commands, or Active Scripts. Each job step can be only one type. Different types of job steps can be combined in the same job.
Job steps execute Transact-SQL statements most often. For example, Transact-SQL statements can be used to back up the database and transaction log, update index statistics, and verify database integrity.
When you define job steps to execute Transact-SQL statements, stored procedures, or extended stored procedures, consider the following facts and guidelines:
SQL Server Agent executes Transact-SQL job steps if the job owner or the database user defined to execute the job step has appropriate permissions.
SQL Server Agent calls the SETUSER statement to set the job owner's database user context for Transact-SQL job steps when
The SETUSER statement goes to the Primary Domain Controller (PDC) or Backup Domain Controller (BDC) to determine the group membership of the job or job step account. SQL Server then determines the associated SQL Server database permissions.
In addition to executing Transact-SQL statements, you can define job steps to execute a program or operating system command. The .EXE, .BAT, .CMD, and .COM extensions identify these programs. When you define a job step that is an operating system command, you should
Many companies have developed business applications with Active Scripting languages, such as Microsoft Visual Basic, Scripting Edition (VBScript), or JavaScript. Creating a job to execute these scripts provides developers with the added benefit of scheduling jobs and notifying operators.
By default, all job owners are allowed to execute job steps that contain operating system commands or Active Script. Jobs owned by login accounts that are members of the sysadmin role execute job steps in the security context of the SQLServerAgent service account. Jobs owned by login accounts that are not members of the sysadmin role execute in the security context of the SQLAgentCmdExec Windows NT user account. This account is created as a local user account, with no administrative privileges, when SQL Server is installed. The account is added as a member of the Users and Domain Users groups.
A user who has no operating system privileges but who has permissions to add a job can create a job that can execute operating system commands or Active Script, with the operating system privileges of the SQLAgentCmdExec account.
You can specify that only members of the sysadmin role have the ability to execute operating system and Active Scripting job steps by modifying the SQL Server Agent properties. Figure 13.3 shows the Job System tab of the SQL Server Agent Properties dialog box with this option checked.
Figure 13.3 Defining job step privileges for the sysadmin role
When creating jobs, you can specify actions that will be taken if a failure occurs during job execution. You can accomplish this by determining the action that should be taken upon the success or failure of each job step:
For example, if the job step requires a connection to a remote server, you could define several retry attempts in case the connection fails.
Figure 13.4 shows an example of logic flow between job steps.
Figure 13.4 Example of logic flow between job steps
A job is executed by SQL Server Agent based on one or more predefined schedules or by a user on demand. You can use SQL Server Enterprise Manager or execute the sp_add_jobschedule system stored procedure to define each job schedule. The job schedules are stored in the msdb..sysjobschedules system table.
A job can be executed by SQL Server Agent as scheduled only when the job and the schedule are enabled.
Jobs can be scheduled to occur
Jobs can also be scheduled to start automatically when
You can define and enable the idle CPU condition for your server in the SQL Server Agent Properties dialog box, as shown in Figure 13.5. The default condition occurs when the average CPU usage remains below 10 percent for 600 seconds.
Figure 13.5 The Advanced tab of the SQL Server Agent Properties dialog box, showing the Idle CPU Condition options
A job can have multiple schedules. For example, a job to back up a database transaction log could be scheduled to execute every two hours during peak business hours, Monday through Friday, and every four hours during non-peak hours. Figure 13.6 shows an example of a job with multiple schedules.
Figure 13.6 Example of a job with multiple schedules
SQL Server Agent captures job and job step execution status and stores the information in the msdb..sysjobhistory system table. You can view history information for individual jobs, as well as configure the size of the job history table, with SQL Server Enterprise Manager.
If a job fails, you can view the job history to obtain information about each job step, the cause for the failure, and solutions to resolve the problem. Specifically, the job history records
If SQL Server shuts down while a Transact-SQL statement is executing, the job history contains information about the job step that was in progress when the shutdown occurred.
The history of SQL Server Agent job executions is stored in the sysjobhistory system table. On the Job System tab of the SQL Server Agent Properties dialog box, you can configure the maximum size of the job history log. The size is specified as the number of rows in the sysjobhistory system table.
Without any defined limits for job history growth, the sysjobhistory table will grow. Depending on the autogrowth settings for the msdb database, the database will then either become full or grow to take up an increasing amount of disk space.
When you configure the size of the job history log, consider the following facts:
NOTE
It is important to check the job step history for failed jobs, as the most accurate description of the reason for the failure will usually be recorded there.
In this exercise, you will create a job that transfers a data file into the Products table in the StudyNwind database and then backs up the database after the data transfer successfully completes.
Option | Tab | Value |
---|---|---|
Step name | General | Copy new product data |
Type | General | Operating System Command (CmdExec) |
Command | General | C:\Sqladmin\Exercise\Ch13\Transfer.cmd |
On success action | Advanced | Go to the next step |
Retry attempts | Advanced | 1 |
Retry interval (minutes) | Advanced | 1 |
On failure action | Advanced | Quit the job reporting failure |
Output file | Advanced | C:\Temp\Prodcopy.txt |
Output file option | Advanced | Overwrite |
Option | Tab | Value |
Step name | General | Backup StudyNwind DB |
Type | General | Transact-SQL Script (TSQL) |
Database | General | StudyNwind |
Command | General | BACKUP DATABASE StudyNwind TO DISK = 'C:\Mssql7\Backup\Nwind.bak' WITH INIT |
On success action | Advanced | Go to the next step |
Retry attempts | Advanced | 1 |
Retry interval (minutes) | Advanced | 1 |
On failure action | Advanced | Quit the job reporting failure |
Output file | Advanced | C:\Temp\Prodcopy.txt |
Output file option | Advanced | Append |
Option | Value |
---|---|
Name | First day of the month |
Schedule Type | Recurring, Monthly, Day 1 of every 1 month. Occurs once at 1:00 A.M. |
In this exercise, you will rename the data transfer file to simulate a failure that causes the first job step to fail.
What do you notice in the history?
Answer
You will correct the error and execute the job again in Lesson 4, "Lesson 4: Troubleshooting SQL Server Automated Administration"
NOTE
If the error message in step 1 is "The system cannot find the path specified," the command in step 1 is incorrect. In this case, edit the step and make sure that the path in the command is correct (it should be C:\Sqladmin\Exercise \Ch13\Transfer.cmd).
What information is displayed in the log?
Answer
What information is displayed in the output file?
Answer
In the following exercises, you will configure the SQL Mail service to see how you can have SQL Server send e-mail messages. You can use this capability to send your own e-mail notifications and results from Transact-SQL scripts and stored procedures.
In this exercise, you will verify that SQL Mail is not running. You will then specify a mail profile name for SQL Mail and start it.
Did a SQL Mail session start successfully?
Answer
What was the source of the error messages associated with SQL Mail?
Answer
You have now configured SQL Mail and SQL Server Agent to use different profiles. This allows them to use separate mailboxes in your messaging system. They can both be configured to use the same profile if you want them to share the same mailbox.
Did the SQL Mail session start successfully?
Answer
In this exercise, you will execute the xp_sendmail extended stored procedure to send the result set of a query to yourself to verify that the SQL Mail session works as expected.
What was the query result?
Answer
Jobs allow you to automate administrative tasks. You can execute jobs on demand or according to a schedule, and a job can generate an e-mail message, Windows NT pop-up message, or a pager message when it succeeds or fails. To use the messaging features, you need to configure operators to receive one of the message types. Jobs provide full multistep capabilities and the ability to control the flow of jobs steps. SQL Server maintains a history of job executions, with messaging indicating success or failure of each job step.