MSSQL - The Magic of sp_help_job !!!

During my journey with SQL Server, I used to query the SQL Server agent jobs metadata, by default it is very important to get some information related to the jobs defined on your SQL instance, the current running ones, some details like steps defined per job, … etc.

Due to all the above situations, I used to design a lot of complex queries that related to SQL jobs and thought that I am doing the right job … but unfortunately I am not.

The secret behind this is simply the system stored procedure sp_help_job which literally save a lot of time and effort of creating such complex long queries, the sp_help_job is a system stored procedure located in msdb system database and has a set of parameters that cover most of the information required for SQL agent jobs.

We can use sp_help_job stored procedure in the below scenarios:

1- Getting all jobs on a SQL instance with some metadata like server, job name, owner, notification methods, create date and last run date … this can be done by executing the stored procedure without specifying any parameters.

exec msdb.dbo.sp_help_job;

2- Getting all current running jobs, this can be done by specifying the parameter @execution_status with value 1.

exec msdb.dbo.sp_help_job @execution_status=1;

3- Getting details for a specific job, by using parameter @job_name and specifying the job name we can get information about the job itself, the jobs steps, the job schedule and the job outcome.

exec msdb.dbo.sp_help_job @job_name=[Job Name];

4- Getting the jobs owned by a specific login, by specifying parameter @owner_login_name.

exec msdb.dbo.sp_help_job @owner_login_name=[Login Name];

5- Getting the enabled or disabled jobs on the instance by specifying parameter @enabled with 0 value for disabled jobs and 1 for the enabled ones.

exec msdb.dbo.sp_help_job @enabled=0;

exec msdb.dbo.sp_help_job @enabled=1;

There are some other parameters that can be specified like the subsystem name, the category name and date created/ modified.

The below snapshot shows all available parameters for sp_help_job stored procedure.