I came across this interesting and detailed article ‘What’s New in SQL Server Agent for Microsoft SQL Server 2005’ on Microsoft TechNet.
This article describes Security Improvements, New Roles in the msdb Database, Multiple Proxy Accounts, Performance Improvements, Performance Counters, New SQL Server Agent Subsystems, Shared Schedules, WMI Event Alerts, SQL Server Agent Sessions, Database Mail Support, Stored Procedure Changes in depth.
What to Check When You Start Using SQL Server Agent
Agent is the scheduler that runs jobs, fires alerts and notifies operators. Its jobs, schedules and history are stored in the msdb database, which is one more reason to back up msdb along with your user databases.
Here is my short checklist for a new server:
- Make sure the Agent service is running and its startup type is Automatic. After a reboot, a stopped Agent means no backups and no maintenance jobs.
- Set up Database Mail and at least one operator, then tell each important job to send a notice when it fails.
- Use the msdb roles SQLAgentUserRole, SQLAgentReaderRole and SQLAgentOperatorRole to give people only the access they need, instead of adding them to sysadmin.
- When a job step must reach files or other servers, run it under a proxy account that has just the rights it needs.
Shared schedules are a small feature that saves time. You define one schedule, like every night at 1 AM, and attach it to many jobs. Change it once and all those jobs follow.
Job history is where most problems show up first. In Management Studio, right click a job and choose View History to see each run and the message from each step. For a quick check across all jobs, the tables msdb.dbo.sysjobs and msdb.dbo.sysjobhistory hold the same details.
One thing surprises people: the Express edition does not include Agent. On Express you need Windows Task Scheduler with sqlcmd to run scheduled work.
Finally, script your jobs. Right click a job, choose Script Job as, then CREATE To, and keep the script with your other server scripts. If you ever rebuild the server or move to a new one, you can recreate every job in minutes instead of clicking through each screen again.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
