This chapter is from the book
This chapter is from the book
This chapter is from the book
Administering the SQL Server Agent
The SQL Server Agent is a Microsoft Windows Service that executes scheduled tasks configured as SQL Server jobs. Ultimately, in SQL Server 2005, any task can be transformed into a job; therefore, the task can be scheduled to reduce the amount of time wasted on manual database administration. The SQL Server Agent can be managed from within SQL Server Management Studio.
Administering the SQL Server Agent Properties
Before utilizing the SQL Server Agent, you should first verify and configure the Agent properties to ensure that everything is copacetic.
The SQL Server Agent Properties dialog box has six pages of configuration settings, described in the following sections.
The General Page
The SQL Server Agent page maintains configurable settings such as Auto Restart SQL Server if It Stops Unexpectedly and Auto Restart SQL Server Agent if It Stops Unexpectedly.
From a best practice perspective, both the restart settings should be enabled on mission-critical databases. This prevents downtime in the event of a server outage because the service will restart if failure is inevitable.
You can change the error log path if preferred and configure a send receipt via the Net send command. In addition, you can include execution trace messages to provide meticulous information on SQL Server Agent operations.
The Advanced Page
The Advanced page controls the behavior of SQL Server Event Forwarding and Idle CPU conditions. It is possible to forward unhandled events, all events, or events based on predefined severity levels selected in the drop-down list to a different server. The target server must be specified in the server drop-down list. The differences between unhandled and handled events are that unhandled events forward only events that no alert responds to, whereas handled events forward both the event and the alert. The final section is tailored toward SQL Server Agent and CPU settings. These settings define the conditions when jobs will run based on values such as Average CPU Usage Falls Below in Percentage and And Remains Below This Level for In Seconds.
The Alert System Page
The Alert System page includes all the SQL Server settings for sending messages from agent alerts. The mail session settings are based on the prerequisite task of configuring SQL Server Database Mail. These topics are discussed in Chapter 21, "Monitoring SQL Server 2005" (online).
The Job System Page
The Job System page controls the SQL Server Agent shutdown settings. You can enter a numeric value based on a time increment that governs how long a job can run before automatically being shut down. It is also possible to specify a nonadministrator Job Step Proxy Account to control the security context of the agent; however, this option is available only when you're managing earlier SQL Server Agent versions.
The Connections Page
The Connections Page should be used to configure a SQL Server alias for the SQL Server Agent. An alias is required only if a connection to the Database Engine will be made without using the default network transport or an alternate named pipe.
The History Page
You should use the final page, History, for configuring the limit size of a job history log setting. The options include setting maximum job history log size in rows and maximum job history rows per job.
Administering SQL Server Agent Jobs
The first subfolder located under the SQL Server Agent is the Job folder. Here, you create new jobs, manage schedules, manage job categories, and view the history of a job.
Follow these steps to create a new job:
- In Object Explorer, first connect to the Database Engine, expand the desired server, and then expand the SQL Server Agent folder.
- Right-click the Jobs folder and select New Job.
- On the General page in the New Job dialog box, enter a name, owner, category, and description for the new job.
- Ensure that the Enabled check box is set to True, as illustrated in Figure 1.16.
Figure 1.16 Specifying the Create New Job Details on the New Job dialog box.
- Click New on the Steps page. When the New Job Steps page is invoked, type a name for the step and enter the type of job this will be. The options range from Transact-SQL, which is the most common, to other items such as stored procedures, Integrations Services packages, and replication. For this example, select TSQL Type and enter the following TSQL syntax in the command window:
BACKUP DATABASE [AdventureWorks] TO DISK = N'C:\Program Files\Microsoft SQL Server \MSSQL.1\MSSQL\Backup\AdventureWorks.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
- From within the General page, parse the command to verify that the syntax is operational and click the Advanced page.
- The Advanced page includes a set of superior configuration settings. For example, you can specify actions on successful completion of this job, retry attempts including intervals, and what to do if the job fails. This page also includes Output File, Log to Table, History, and the potential to run the job under a different security context. Click OK to continue.
- Within the New Job dialog box, you can use the Schedules page to view and organize schedules for the job. Here, you can create a new schedule or select one from an existing schedule.
- Click OK to finalize the creation of the job.
)
Enabling or Disabling a SQL Server Agent Job
Each SQL Server Agent job can be either enabled or disabled by right-clicking the job and selecting either Enable or Disable.
Viewing SQL Server Agent Job History
From a management perspective, you need to understand whether a SQL Server Agent job was fired properly, completed successfully, or just outright failed. The Job History tool, which is a subcomponent of the Log File Viewer, provides thorough diagnostics and status of job history. Perform the following steps to review job history for a SQL Server Agent job from within SQL Server Management Studio:
- In Object Explorer, first expand the SQL Server Agent and then the Jobs folder.
- Right-click a desired job and select View Job History.
- In the Log File Viewer, review the log file summary for any job from within the center pane.
- Choose from additional options such as loading saved logs, exporting logs, creating a filter, parsing through logs with the search feature, and deleting logs.
Administering SQL Server Alerts and Operators
The SQL Server Alerts and Operators folders are used for monitoring the SQL Server infrastructure by creating alerts and then sending out notifications to operators. For more information on creating alerts and operators, review Chapter 21.
Administering SQL Server Proxies
The Proxies Folder found within the SQL Server Agent enables you to view or modify the properties of the SQL Server Agent Proxy account. You enter a proxy name and credentials and select the subsystem the proxy account has access to.
Administering SQL Server Error Logs
The final folder in the SQL Server is Error Logs. You can configure the Error Logs folder by right-clicking the folder and selecting Configure. The configuration options include modifying the error log file location, reducing the amount of disk space utilized by enabling the option Write OEM Error Log, and changing the Agent Log Level settings. These settings include enabling Error, Warnings, and/or Information.
Perform the following steps to view SQL Server Agent Error Logs:
- In Object Explorer, first expand the SQL Server Agent and then the Error Logs folder.
- When all the error logs are listed under the Error Logs folder, double-click any of the error logs to view them.