Lesson 2: Automating Routine Maintenance Tasks

[Previous] [Next]

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

Creating Operators to Notify

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:

  • You can use a group e-mail alias to notify more than one individual to respond to potential problems.
  • You should test each notification method used to notify the operator to ensure that the operator is able to receive messages.
  • You should specify an on-duty schedule for each operator to be notified by pager. If a job that is defined to notify an operator by pager conflicts with the operator's on-duty schedule, the notification fails.
  • Use a Windows NT net send command to send messages to network operators. (This is not supported in Windows 95 and Windows 98.)

Troubleshooting Operator Notifications

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

  • Ensure that the operator is available to receive notifications
  • Review the most recent notification attempts to determine the date and time of the last notification
  • Test individual notification methods outside of SQL Server by verifying that you can send e-mail messages, page an operator, or successfully execute a net send command

Exercise: Creating an Operator

In this exercise, you will create an operator, send a message, and verify that the message was received.

  • To create an operator

  1. In the console tree, expand Management, and then expand SQL Server Agent.
  2. Right-click Operators, and then click New Operator.
  3. On the General tab, in Name, enter the name Admin.
  4. In Email Name, enter the operator's mailbox address, Admin. (Or click the ellipsis button and add Admin from the address book.)
  5. In Net Send Address, enter the computer name SQLServer (or the name of your computer if it is not SQLServer).
  6. Click Test for both e-mail and the net send command. Click OK to close the resulting dialog boxes, and finally, click OK to close the New Operator Properties dialog box and add the new operator.
  7. Check the Inbox to see that the test message was received from SQL Server Agent.

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.

Creating Jobs

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

  • Ensure that a job is enabled. Jobs are enabled by default. If a job is disabled, it cannot execute as scheduled. However, a disabled job can be executed when an alert fires or when a user starts the job in SQL Server Enterprise Manager.
  • Specify the owner who is responsible for performing the job. By default, the owner is the Windows NT or SQL Server user login account creating the job.
  • Define whether the job executes on a local server or on multiple remote servers.
  • Create job categories to help you organize, filter, and manage many jobs. For example, you can create job categories that correspond to the departments in your company. Categories are particularly useful when automating jobs in a multiserver environment.

Exercise: Defining a Job with the Create Job Wizard

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.

  • To use the Create Job Wizard

  1. In the console tree, click your server.
  2. On the SQL Server Enterprise Manager Tools menu, click Wizards.
  3. Expand Management, and then double-click Create Job Wizard.
  4. Use the information in the following table to create a job that is scheduled to back up the StudyNwind database transaction log daily at 5:00 p.m. Accept the defaults for any options that are not listed.
  5. 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

Exercise: Executing the Job Manually

In this exercise, you will verify that your job was created successfully and that notifications were sent.

  • To execute the job manually

  1. Verify that the trunc. log on chkpt. database option of the StudyNwind database is false.
  2. In SQL Server Enterprise Manager, expand Management, and then expand SQL Server Agent.
  3. In the console tree, click the Jobs icon to display all defined jobs in the details pane.
  4. In the details pane, right-click the StudyNwind Log Backup job, and then click Start Job. This executes the job manually.
  5. Note that a message appeared on your screen from a net send command.

  6. In the details pane, right-click the StudyNwind Log Backup job, and then click View Job History to verify that the job completed successfully.
  7. What information is displayed in the job history when Show Step Details is selected? What information is displayed when this option is cleared?

    Answer

  8. Switch to the Inbox to verify that you received an e-mail message.

Defining Job Steps

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.

Using Transact-SQL Statements

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:

  • You should include required variables and parameters in the job step.
  • You can send the result set of a job step to an output file. Output files are often used in troubleshooting to capture any error messages that may have occurred while the statement was executing. You cannot use an output file from one job step as input into a succeeding step.

Specifying Database User Context to Execute Job Steps

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 job is owned by a SQL Server login account that is not a member of the sysadmin role
  • A member of the sysadmin role owns the job, but the individual Transact-SQL step has been defined to execute under another database user context
  • The job is owned by a Windows NT user account that is not a member of the sysadmin role

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.

Using Operating System Commands

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

  • Identify a process exit code to indicate that the command was successful.
  • Include the full path to the executable program in the Command text box when you start an operating system command. The path is required to help SQL Server Agent find the program source.

Using Active Scripting Languages

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.

Permissions Required to Execute Operating System or Active Scripting Job Steps

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

Determining Action Flow Logic for Each Job Step

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:

  • By default, SQL Server advances to the next job step upon success and stops upon failure of a job step. However, job steps can go to any step defined in the job upon success or failure.
  • You can specify the number of times that SQL Server should attempt to retry execution of a job step if the step fails. You can also specify the retry intervals (in minutes).
  • 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.

click to view at full size.

Figure 13.4 Example of logic flow between job steps

Scheduling Jobs

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

  • At a specific time (one time only)
  • On a recurring basis (daily, weekly, or monthly)

Jobs can also be scheduled to start automatically when

  • SQL Server Agent is started
  • The CPU is idle

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

Multiple Schedules

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.

click to view at full size.

Figure 13.6 Example of a job with multiple schedules

Reviewing and Configuring Job History

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.

Reviewing an Individual Job History

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

  • The date and time that the job step occurred
  • Whether the job step failed or succeeded
  • The operator who was notified and the notification method
  • The duration of the job step
  • Errors or messages

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.

Configuring the Size of the Job History Log

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:

  • By default, the maximum size of the job history log is set at 1000 rows.
  • By default, the maximum job history for individual jobs is set at 100 rows.
  • Rows will be removed from the sysjobhistory system table in a first-in, first-out (FIFO) manner when the limits are reached.

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.

Exercise: Creating a Job with Multiple Job Steps

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.

  • To create a job with multiple job steps

  1. In SQL Server Enterprise Manager, expand Management, and then expand SQL Server Agent.
  2. Right-click Jobs, and then click New Job.
  3. In the New Job Properties dialog box, on the General tab, enter the name StudyNwind Monthly Data Transfer in Name. Leave the other options on the General tab as they are.
  4. Use the information in the following tables to add two new steps on the Steps tab. Leave options that are not listed as they are.
  5. 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

  6. Use the information in the next table to create a new schedule on the Schedules tab. Leave options that are not listed as they are.
  7. 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.

  8. On the Notifications tab, check E-mail Operator and Net Send, and enter Admin as the address for each.
  9. Click OK to close the New Job Properties dialog box and add the new job.

Exercise: Simulating a Job Step Failure

In this exercise, you will rename the data transfer file to simulate a failure that causes the first job step to fail.

  • To simulate a failure and verify that a job step failed

  1. Open Windows NT Explorer.
  2. Rename the file C:\Sqladmin\Exercise\ch13\Prods.txt to C:\Sqladmin\Exercise \Ch13\Prods.sav.
  3. Switch to SQL Server Enterprise Manager.
  4. Right-click the StudyNwind Monthly Data Transfer job, and then click Start Job. The Start Job dialog box allows you to start a job at any step. Click Start to have the job execute starting with the first step.
  5. You will have to wait for about a minute before a message appears on your screen from a net send command. This is because the job was set to retry once if it fails. The notification is sent only if the retry also fails.
  6. In the details pane, right-click the StudyNwind Monthly Data Transfer job, and then click View Job History to verify that the job did not complete successfully. The job will appear in the history only after the retry, but the initial failure will be seen immediately if the Show Step Details option is checked.
  7. 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).

  8. Open the Windows NT application log to confirm that the job failure was logged.
  9. What information is displayed in the log?

    Answer

  10. Switch to Inbox to confirm that you received an e-mail message notifying you that the job failed.
  11. Open Notepad, and then open C:\Temp\Prodcopy.txt.
  12. What information is displayed in the output file?

    Answer

Configuring SQL Mail

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.

Exercise: Starting the SQL Mail Session

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.

  • To start the SQL Mail session

  1. Switch to SQL Server Enterprise Manager.
  2. Expand Support Services. Note that SQL Mail is not running.
  3. Right-click SQL Mail, and then click Start.
  4. Did a SQL Mail session start successfully?

    Answer

  5. Review the messages in the current log in SQL Server Logs in the Management folder in the console tree.
  6. What was the source of the error messages associated with SQL Mail?

    Answer

  7. Return to the Support Services folder. Right-click SQL Mail, and then click Properties.
  8. In the Profile Name, select SQLMail Profile, and then click Test. A message confirms that a MAPI session was started using the profile. Click OK to close the dialog box.
  9. 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.

  10. Right-click SQL Mail, and then click Start.
  11. Did the SQL Mail session start successfully?

    Answer

Exercise: Sending a Query Result Using SQL Mail

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.

  • To send a query result using SQL Mail

  1. Open SQL Server Query Analyzer, and log on to the (local) server with Microsoft Windows NT authentication. Your Administrator account is a member of the Windows NT Administrators group, which is automatically mapped to the SQL Server sysadmin role.
  2. Open C:\Sqladmin\Exercise\Ch13\Sqlmail.sql, and review its contents.
  3. Execute the script. You should see a message in the results pane indicating that the message was sent.
  4. Switch to Inbox and open the new message.
  5. What was the query result?

    Answer

Lesson Summary

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.



Microsoft Press - Microsoft SQL Server 7. 0 System Administration Training Kit
Microsoft SQL Server 7.0 System Administration Training Kit
ISBN: 1572318279
EAN: 2147483647
Year: 1999
Pages: 100

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net