Tag Archive for: sql jobs

Query SQL Server Job Agent Execution Information and History

09 Nov
November 9, 2012

There are multiple ways to access SQL Job Agent jobs execution statistics, such as:

  • Through SQL Server Management Objects (SMO): which provide a programmatic way of accessing and controlling many SQL Server objects. The Smo.Agent namespace will provide the required objects for accessing job statistics such as the last time each job (or step) ran, the duration the job ran for and the returned status as well as many other relevant information regarding job execution.
  • Through undocumented Stored Procedures such as master.dbo.xp_sqlagent_enum_jobs: Although this doesn’t provide a lot of flexibility, particularly if you are executing and consuming the data within the context of T-SQL, rather than programmatically through wrapper classes that simplifies handling stored procedures with multiple output tables. It is important to note that since this stored procedure is undocumented, Microsoft could potentially retire it without prior notice, leaving your code non-functional.
  • Through Job Agent System Tables: In my opinion this provides the best and most supported method of access. for the rest of this post I will be discussing the different System Tables provided to access SQL Server Job Agent statistics and execution information.