With most, if not all organizations, when executing processes during non business hours it is imperative to know if and when the these processes fail. At a minimum, full backups are typically executed off hours, but many organizations execute resource intensive batch processes which are critical to the core business for the subsequent day. With staff members stretched to meet numerous needs, validating that the jobs have executed successfully on a daily basis can be time consuming, but it is often better to know about the failures ahead of time rather than having the users notify IT. The typical solution to determine if a job fails is to notify a SQL Server operator, but SQL Server Agent mail is not always as reliable as needed. As such, in this tip we will provide scripts to determine recent SQL Server Agent Job failures.
Since SQL Server stores the Job information in a set of tables in the MSDB database, it is possible to query these tables to determine the status of the Jobs. As such, below outlines the Job related tables in the MSDB database:
SQL Server Agent Tables
|ID||Purpose||SQL Server 2000 Table||SQL Server 2005 Table|
|1||Primary table for job related information||MSDB.dbo.sysjobs||MSDB.dbo.sysjobs|
|2||Entry for each step in a specific job||MSDB.dbo.sysjobsteps||MSDB.dbo.sysjobsteps|
|3||Schedule(s) for each job||MSDB.dbo.sysjobschedules||MSDB.dbo.sysjobschedules|
|4||Local or remote servers where the job executes||MSDB.dbo.sysjobservers||MSDB.dbo.sysjobservers|
|5||Historical record of the job's execution||MSDB.dbo.sysjobhistory||MSDB.dbo.sysjobhistory|
|6||Current job status, next run date\time, queued date, etc.||Not applicable||MSDB.dbo.sysjobactivity|
|7||Historical job step log information for all job steps configured to write to this table||Not applicable||MSDB.dbo.sysjobstepslogs|
SQL Server 2000 and 2005 Code Snippets
SQL Server 2000 Code Snippet
SQL Server 2005 Code Snippet
- It is imperative to check your jobs on a daily basis, so determine the best means for your organization to automate that process.
- For advanced job chaining, job management, alerting, performance metrics research the Job Management applications in the market place.
- If you are not taking full advantage of SQL Server Agent, stay tuned for additional MSSQLTips on the topic. For now, check out the following tips:
Last Update: 10/12/2006
About the author
View all my tips