Home > SQL Tips > Using system SPs to get details of SQL Jobs, Steps, Commands, Schedule, Last run details, etc

Using system SPs to get details of SQL Jobs, Steps, Commands, Schedule, Last run details, etc


Sometimes it get difficult or time-taking to get each and every SQL Job details, its schedule and other information. Also checking all these information in SSMS is time taking specially when your machine is running a client, and the server is located in some remote place. On the other side if you get some handy system Queries you can fire them in SSMS and get the desired results immediately, without browsing too much on heavy GUI tools.

Here are some of the System Stored Procedure queries that can be used to check the SQL Job details, like:

1. Job Details:
– Job ID/Name/Description
– Category
– Date Created/Modified
– Last/Next run Date/Time
– Current execution status

2.Job Steps
– Step ID/Names
– SQL Commands in each step
– On Success/Fail action
– Last run Duration/Retries/Date/Time

3. Job Schedule:
– Schedule ID/Name/Description
– Enable/Disable Status
– Job Frequency/Interval
– Job Start Date/Time
– Next run Date/Time
 

–> sp_help_job: system SP gives you all these information in 4 different result sets as shown in the image below. You can pass Job-Name or Job-ID as parameter to the system SP.

exec msdb.dbo.sp_help_job @job_name = 'Acquire_Source_Data'
-- OR --
exec msdb.dbo.sp_help_job @job_id = 'ad8252d8-35d8-4c1f-b603-db0dfebd5e27'

SQL Jobs 01
 

–> sp_help_jobstep & sp_help_jobschedule: these 2 SPs also gives similar information provided by above SP (sp_help-job), but with single result-set. So you can execute them independently and use them in your logic.

exec msdb.dbo.sp_help_jobstep @job_id = 'ad8252d8-35d8-4c1f-b603-db0dfebd5e27'

exec msdb.dbo.sp_help_jobschedule @job_id = 'ad8252d8-35d8-4c1f-b603-db0dfebd5e27'
--

SQL Jobs 02


  1. No comments yet.
  1. July 9, 2016 at 3:36 am
  2. April 2, 2016 at 2:11 am
  3. March 22, 2016 at 7:01 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: