Hello Geeks,
I would like to thank Amit Sir for giving me the opportunity to blog on SQL Server topics. This being my first blog, I am staring with SQL server system stored procedures.
Today I will cover SQL Server sp_help_job system stored procedure. In our work place we have lot of SQL jobs. Some of them runs whole day long and some just for a moment. They will load the data and generate extracts which will be used by business people. Thus, it becomes quite essential to support the system efficiently so that the job finishes in time and as desired. Also, while designing a new system sometimes we need to check the dependency of one job over another. In such scenario we need to check whether a particular job has finished or not.
Here is a simple script using sp_help_job to display information about jobs that are used by SQL Server Agent:
USE msdb EXEC dbo.sp_help_job
This procedure gives some insight into the status, and information, about a job. This stored procedure provides information such as last start time, job status etc.
Look for ‘current_execution_status’ to get the status of the job.
Value Description
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.
Syntax:
sp_help_job [ [ @job_id = ] job_id ] [ , [ @job_name = ] ‘job_name’ ] [ , [ @job_aspect = ] ‘job_aspect’ ] [ , [ @job_type = ] ‘job_type’ ] [ , [ @owner_login_name = ] ‘login_name’ ] [ , [ @subsystem = ] ‘subsystem’ ] [ , [ @category_name = ] ‘category’ ] [ , [ @enabled = ] enabled ] [ , [ @execution_status = ] status ] [ , [ @date_comparator = ] ‘date_comparison’ ] [ , [ @date_created = ] date_created ] [ , [ @date_last_modified = ] date_modified ] [ , [ @description = ] ‘description_pattern’ ]
The sp_help_job stored procedure has a parameter named execution_status that can be used to return information for all jobs that have a particular status. For example, command to retrieve job information for all jobs that currently have a status of “Executing”:
exec msdb.dbo.sp_help_job @execution_status = 1
Like this you can pass different arguments to retrieve different information about the job.
Hope this script proves helpful to you.
Regards
Arup Dolui
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
