Create New Job
Creating a new job should be self explanatory. Here we will use the example of a job to recycle logs. The typical process to create a new job is as follows.
- Expand “SQL Server Agent”.
- Right-click on “Jobs”.
- Select the “New Job…” menu option.
- Set “Name” to “RecycleErrorLog” and “Owner” to Windows or SQL Server user, for example “sa”. The owner will determine what functionality is possible.
- Assign a category if you like to use them. Defining your own categories is described below.
- Click on the “Steps” page and click the “New” button.
- Set the “Step name” to “Step_1” and set the Command to “sp_cycle_errorlog”. If you want any advanced actions, click the “Advanced” page and change the settings.
- Click the “OK” button.
- Click on the “Schedules” page and click the “New” button.
- Set the Name to “DailyMidnight”.
- Change “Occurs” to “Daily” and leave the default “Occurs once at” as “00:00:00”.
- Click the “OK” button.
- If you need any notifications, use the “Notifications” page to configure them.
- Click the “OK” button on the “New Job” dialog.
Monitor Jobs
The “Job Activity Monitor” allows you to track the status of previous and current job runs.
- Expand “SQL Server Agent > Jobs”.
- Right-click on “Job Activity Monitor”.
- Select the “View Job Activity” menu option.
- Click the “Refresh” button if you are tracking running jobs.
Schedules
SQL Server allows you to create defined schedules that can be used by several jobs.
- Expand “SQL Server Agent”.
- Right-click on “Jobs”.
- Select the “Manage Schedules” menu option.
- Click the “Refresh” button if you are tracking running jobs.
- The screen lists all existing schedules, along with the jobs they are linked to. Clicking on the “Job in schedule” link gives you a list of the jobs for that schedule.
- To create a new schedule, click the “New” button. The subsequent screen gives the standard schedule definition screen. Make sure you give is a reasonable name.
Categories
You can associate jobs with categories, and even define your own categories. This doesn’t alter their functionality or security. It’s just a way to group them.
- Expand “SQL Server Agent”.
- Right-click on “Jobs”.
- Select the “Manage Categories” menu option.
- You can “Add” new categories, or highlight an existing category and click “View Jobs” to see the jobs assigned to that category.
Operators
Operators allow you to define individuals, or groups of individuals using mailing lists, who require notifications.
- Expand “SQL Server Agent”.
- Right-click on “Operators”.
- Select the “New Operator” menu option.
- Enter the “Name” of the operator, along with their contact details, typically the email address.
- Make sure the operator is enabled.
- Click the “OK” button.
With the operator in place, you can assign the operator to the notification for a job.
- Right-click on the job of interest.
- Select the “Properties” menu option.
- Click on the “Notifications” page.
- Check the “E-mail” option and select the operator you just created. Select the condition associated with the notification, for example, “When the job fails”.
- Click the “OK” button.
You need to make sure the agent is configured to send emails. This assumes Database Mail is already configured.
- Right-click on the “SQL Server Agent”.
- Select the “Properties” menu option.
- Click the “Alert System” page.
- Check the “Enable mail profile” option and select the “Mail system” of “Database Mail” and “Mail profile” you have defined for this system.
- There are additional mail settings you can alter, but they are not necessary.
- Click the “OK” button.
Agent Properties
You will probably leave the defaults for most systems.
- Right-click on the “SQL Server Agent”.
- Select the “Properties” menu option.
- On the “General” page, make sure the “Auto restart…” options are checked.
- On the “History” page, you may want to check the “Remove agent history” option and specify an “older than”.
- Click “OK” when you are finished.
For more information see: