Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Queries to inventory your SQL Server Agent Jobs


By:   |   Last Updated: 2017-07-21   |   Comments (4)   |   Related Tips: More > SQL Server Agent

Problem

I am a SQL developer who recently migrated to a team that relies heavily on SQL Server Agent Jobs. I need some help with inventorying the jobs on a SQL Server Agent installation. Please provide some code samples that illustrate how to inventory programmatically the jobs on a SQL Server Agent installation.

Solution

SQL Server offers a collection of tables in the msdb database that let you inventory the jobs on a SQL Server Agent installation. This tip will introduce you to some of these tables and demonstrate simple programming examples to inventory SQL Server Agent Jobs. The specific tables covered by this tip include:

  • msdb.dbo.sysjobs
  • msdb.dbo.sysjobsteps
  • msdb.dbo.sysjobschedules

The scripts in this tip present code samples for using the tables separately and jointly to discover such information as: when jobs were created, last used, and will be used next. These tables additionally support the discovery of other information about the jobs on a SQL Server Agent installation.

All code samples use a SQL Server Agent with jobs created within either of two prior MSSQLTips.com articles: one on getting started with SQL Server Agent and another on creating multistep and dynamic jobs.

There are only about a handful of jobs on the SQL Server Agent used to demonstrate code samples within this article. Personal experience confirms that the tips apply to SQL Server Agent installations with at least scores of jobs. The actual maximum number of jobs to which the tips apply is very likely much larger than several score of jobs.

What jobs are in SQL Server Agent?

The sysjobs table in the msdb database stores selected top-line information about the jobs in SQL Server Agent. There is a single row in the sysjobs table for each job within a SQL Server Agent. The field values for each row identify or describe the jobs on a SQL Server Agent.

Here's a summary of the sysjobs fields used in the code sample for this table. None of these values can be NULL.

  • Job_id has a uniqueidentifier data type that is a unique id field value for a job on a SQL Server Agent; this field is especially useful for joining msdb tables with different information about the jobs for SQL Server Agent.
  • Name is a string of Unicode characters designating a job. The field has a sysname data type, which is equivalent to a nvarchar(128) data type that does not allow NULL values. Consequently, every SQL Server Agent Job must have a name field value, and the value cannot exceed 128 Unicode characters.
  • Enabled is a tinyint field denoting whether a job can be invoked by a schedule. A value of 1 allows the job to be started on a schedule. A value of 0 means the job cannot be invoked by a schedule, but a SQL Server Agent user can manually start the job. Even if a job is enabled, it is not necessary for the job to have a schedule, and the job can be started manually.
  • Date_created and date_modified are two fields with datetime data types that indicate, respectively, when a job was first created and when it was last modified.

The following script lists five field values from the sysjobs table for each job on a SQL Server Agent. The result set for the script displays the jobs in ascending order by creation date.

-- list of jobs; selected info about jobs
SELECT 
 job_id
,name
,enabled
,date_created
,date_modified
FROM msdb.dbo.sysjobs
ORDER BY date_created

Here's the result set from the preceding script. Notice that the syspolicy_purge_history job has the earliest creation date on the SQL Server Agent. This is a Microsoft-supplied job that is created when you first install SQL Server Agent on a SQL Server instance. The other jobs listed are custom ones created for the two previously referenced MSSQLTips.com articles introducing how to create SQL Server Agent jobs.

Create a summary table is the first custom job created. It was created on March 25, 2017. This job is enabled (it has a value of 1) when the screen shot was taken. The next job created is named Populate the Inserts Per Day table. This job is not enabled so that it has to be launched manually or on demand. If there is a schedule assigned to the job, it will not be able to launch the job until the enabled field value is switched from 0 to 1. The last two jobs (Create a two-step reporting job and Four step job with error branching) are described in the preceding SQL Server Agent tip article within this series.

The date_modified column value is always later than the date_created column value. From the time of the initial creation of the Four step job with error branching job until the last modification date_time is nearly one full month. This job experienced an especially long development period with several rounds of tweaking and testing.

results

When was the last run date and time for a SQL Server Agent Job?

The date_created and date_modified field values from the sysjobs table provide some information about how recently a job was created and when it was last modified to keep it current with new requirements. However, sometimes a job may not be created nor modified recently, but it can still be used on a regular basis. One way of getting a handle on if a job is still being used no matter how long ago the job creation date or last modified date is to check its last run date and time. Jobs with a last run date and time close to now are likely still being used on a regular basis.

The msdb.dbo.sysjobsteps table includes last_run_date and last_run_time fields. The sysjobsteps table has a separate row for each step within each job. If a job has just one step, then there is just one row for the job. When a job has more than one step, then there is one row in the sysjobsteps table for each step within in a job. Rows within the sysjobsteps table can be uniquely identified by their job_id and step_id column values.

By joining the sysjobsteps table to the sysjobs table, you can expand the amount of information displayed for jobs on SQL Server Agent. For example, the last run date and time are not available from the sysjobs table. By joining the sysjobsteps table to the sysjobs table, you can display the last run date and time values along with those job properties from the sysjobs table.

The sysjobsteps code samples reference five columns from the sysjobsteps table. None of these values can be NULL.

  • The Job_id column value for a job has the same uniqueidentifier value as in the sysjobs table. This feature facilitates jointly displaying last_run_date and last_run_time values along with other job properties, such as job name.
  • Step_id is an int data type value denoting the order of a step within a job.
  • Step_name is a sysname data type denoting the name of a step within a job.
  • Last_run_date is an int value denoting the date that a job was last run. Its format is yyyymmdd.
  • Last_run_time is an int value indicating the time when a job step was last run on its last run date. Its format is hhmmss. The last_run_time value refers to the start time for the job step.

While last_run_date and last_run_time column values denote datetime values, they have an int data type instead of a datetime data type. For informal reports or job step reviews by database administrators, the int data type representations are likely to be acceptable. However, for reviews by non-technical colleagues, it may sometimes be more informative to represent last_run_date and last_run_time values with a more traditional date or time representation.

The following script displays the five sysjobsteps column values referenced above along with two additional conversion columns. The conversion columns (converted_last_run_date and converted_last_run_time) are for representing in traditional formats last_run_date as a date value and last_run_time as a time value. Therefore, last_run_date appears as yyyy-mm-dd instead of yyyymmdd and last_run_time appears as hh:mm:ss instead of hhmmss.

-- job_ids with job steps showing last run date/time 
-- before and after conversion for display
SELECT 
sysjobsteps.job_id
,sysjobsteps.step_id
,sysjobsteps.step_name
,sysjobsteps.last_run_date
,LEFT(CAST(sysjobsteps.last_run_date AS VARCHAR),4)+ '-'
+SUBSTRING(CAST(sysjobsteps.last_run_date AS VARCHAR),5,2)+'-'
+SUBSTRING(CAST(sysjobsteps.last_run_date AS VARCHAR),7,2) converted_last_run_date
,sysjobsteps.last_run_time
,
CASE 
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 6  
  THEN SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,2) 
    +':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),3,2)
    +':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),5,2)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 5
  THEN '0' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,1) 
    +':'+SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),2,2)
    +':'+SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),4,2)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 4
  THEN '00:' 
    + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,2)
    +':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),3,2)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 3
  THEN '00:' 
    +'0' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,1)
    +':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),2,2)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 2  THEN '00:00:' + CAST(sysjobsteps.last_run_time AS VARCHAR)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 1  THEN '00:00:' + '0'+ CAST(sysjobsteps.last_run_time AS VARCHAR)
END converted_last_run_time
FROM msdb.dbo.sysjobsteps
ORDER BY job_id, step_id

The following screen shot shows the result set from the preceding script. The first thing to note is that there are 15 rows in this result set instead of only the 9 rows from the sysjobs script result set. The reason for the extra rows in this result set is that jobs with multiple steps occur once for each step. For example, the 2139A348-31AB-4098-9902-2D4A8BC3EAA3 uniqueidentifier appears for the first 3 rows in the result set. As you can tell from the result set for the sysjobs script, this job_id value denotes the syspolicy_purge_history job. The job has three distinct steps and three corresponding rows in the sysjobsteps script result set.

The eighth and ninth rows of the result set both point to job steps with a step_name of Create a fresh summary table. Although step names values are identical, the job_id values are distinct between the eighth and ninth rows. You can interpret this as meaning that there are two different jobs each of which contains an identically named job step. The eighth row points to a single-step job named Create a summary table. The ninth row points to the first step in a two-step job named Create a two-step reporting job. See the previously displayed result set from the sysjobs table query to map job_id values to job names.

Now that you have a grasp of how to relate column values to jobs and job steps, we can move on to the main reason for the preceding script, which is to display the last_run_date and last_run_time field values - in either of two formats. The last_run_date column displays values in their native int data type format (yyyymmdd). The converted_last_run_date column shows values in what looks like a traditional SQL Server date format (yyyy-mm-dd). If you examine the preceding script, you can tell that the converted values are not really date values - instead they are varchar values. A string expression performs the transformation. If you need values in a date data type instead of a varchar data type, you can wrap the string expression in a CONVERT or CAST function.

The last two columns of the result set in the following screen shot reveal the last_run_time and converted_last_run_time for the job steps. Again, a string expression converts an int value representing a time value to a varchar string representation. When the time is midnight, the last_run_time value appears as 0 or 00:00:00. When the last_run_time value is one second after midnight, the last_run_time value appears as 1 or 00:00:01. The three job steps in the syspolicy_purge_history job all start within the same second of 2 AM. On the other hand, the four steps in the Four step job with error branching job (uniqueidentifier value of 74FAA7A4-A184-4D35-807E-682E602FA0ED) run at about 10-second intervals. This is consistent with the code for the job (see this link for more detail on the job).

messages

The following script demonstrates how to join a result set from the sysjobs table with a result set from the sysjobsteps table. Notice particularly the FROM clause design. An INNER JOIN operator joins the sysjobsteps table to the sysjobs table. The join is on the job_id column value from each table.

-- list of jobs with names and job steps 
-- showing date created/modified, last_run_date/time of job steps
SELECT 
name
,CONVERT(VARCHAR(16), date_created, 120) date_created
,CONVERT(VARCHAR(16), date_modified,120) date_modified
,sysjobsteps.step_id
,sysjobsteps.step_name
,LEFT(CAST(sysjobsteps.last_run_date AS VARCHAR),4)+ '-'
+SUBSTRING(CAST(sysjobsteps.last_run_date AS VARCHAR),5,2)+'-'
+SUBSTRING(CAST(sysjobsteps.last_run_date AS VARCHAR),7,2) last_run_date
,
CASE 
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 6  
  THEN SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,2) 
    +':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),3,2)
    +':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),5,2)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 5
  THEN '0' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,1) 
    +':'+SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),2,2)
    +':'+SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),4,2)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 4
  THEN '00:' 
    + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,2)
    +':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),3,2)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 3
  THEN '00:' 
    +'0' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,1)
    +':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),2,2)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 2  THEN '00:00:' + CAST(sysjobsteps.last_run_time AS VARCHAR)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 1  THEN '00:00:' + '0'+ CAST(sysjobsteps.last_run_time AS VARCHAR)
END last_run_time

FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobsteps
ON sysjobs.job_id = sysjobsteps.job_id
ORDER BY sysjobs.job_id,sysjobsteps.step_id

The next screen shot shows the result set generated by the script joining the sysjobsteps table to the sysjobs table. The main feature to notice is that the result set contains column values based on both tables. For example, because the job name field value from the sysjobs table appears in the result set, there is no need to display the job_id column values from the sysjobsteps table to indicate the job to which a job step belongs. Also, the result set shows in a single row date_created and date_modified column values from the sysjobs table along with last_run_date and last_run_time column values from the sysjobsteps table.

This following screen shot was taken one day later than the preceding one. Therefore, job steps scheduled to run on both days have different last_run_date column values between the two result sets in the two screen shots. For example, the syspolicy_purge_history job has a last_run_date of 2017-07-06 in the following screen shot but a value of 2017-07-05 in the tip's first screen shot. Also, the last_run_time values can change slightly according to precisely when in a second the preceding successful job step started and precisely how long it took to complete.

purge history

When is the next run date and time for a SQL Server Agent Job?

Another question that can be asked about SQL Server Agent jobs is: when will they run next? SQL Server Agent can only provide an answer to this question for jobs that have a schedule associated with them. The scheduled time for the job to start running is the time for a job to run. So long as SQL Server Agent is started on a computer, it will launch its enabled jobs on their scheduled time.

SQL Server Agent stores schedule information about jobs within the sysjobschedules table of the dbo schema in the msdb database. There are just four columns in this table; their descriptions appear below.

  • Schedule_id denotes a schedule with an int data type value. This column references the schedule_id column within the sysschedules table, which has more detailed information about the schedules on a SQL Server Agent installation. More than one job can run on the same schedule. Also, when there is no schedule assigned to a job, the job does not appear in the sysjobschedules table.
  • Job_id has a uniqueidentifier data type that references the job_id column within the sysjobs table.
  • Next_run_date and next_run_time have int data type values to indicate the next date and time when a job is scheduled to run. Their representations are the same as for last_run_date and last_run_time in the sysjobsteps table. You can convert next-run date and time int values to what looks like date and time format for display with the same technique demonstrated for converting last_run_date and last_run_time.

The following script displays the rows of the sysjobschedules table for the jobs within SQL Server Agent. The script shows the string expression to convert the next_run_date and next_run_time values from int data type values to varchar data type values. Notice the syntax of the conversion string expressions are the same as for the last_run_date and last_run_time expressions.

-- schedule_id, job_ids showing next run date/time with conversion for display
SELECT 
schedule_id
,job_id
,LEFT(CAST(next_run_date AS VARCHAR),4)+ '-'
+SUBSTRING(CAST(next_run_date AS VARCHAR),5,2)+'-'
+SUBSTRING(CAST(next_run_date AS VARCHAR),7,2) next_run_date
,
CASE 
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 6  
  THEN SUBSTRING(CAST(next_run_time AS VARCHAR),1,2) 
    +':' + SUBSTRING(CAST(next_run_time AS VARCHAR),3,2)
    +':' + SUBSTRING(CAST(next_run_time AS VARCHAR),5,2)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 5
  THEN '0' + SUBSTRING(CAST(next_run_time AS VARCHAR),1,1) 
    +':'+SUBSTRING(CAST(next_run_time AS VARCHAR),2,2)
    +':'+SUBSTRING(CAST(next_run_time AS VARCHAR),4,2)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 4
  THEN '00:' 
    + SUBSTRING(CAST(next_run_time AS VARCHAR),1,2)
    +':' + SUBSTRING(CAST(next_run_time AS VARCHAR),3,2)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 3
  THEN '00:' 
    +'0' + SUBSTRING(CAST(next_run_time AS VARCHAR),1,1)
    +':' + SUBSTRING(CAST(next_run_time AS VARCHAR),2,2)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 2  THEN '00:00:' + CAST(next_run_time AS VARCHAR)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 1  THEN '00:00:' + '0'+ CAST(next_run_time AS VARCHAR)
END next_run_time
FROM msdb.dbo.sysjobschedules
ORDER BY job_id

The result set for the sysjobschedules script appears next. Notice that there are just six jobs listed in the result set while the sysjobs table contains nine rows. This disparity results from the fact that three jobs of the nine jobs within SQL Server Agent have no schedule associated with them. They are meant to be launched on demand - not on a schedule.

Unlike last_run_date and last_run_time from the sysjobsteps table, next_run_date and next_run_time from the sysjobschedules table do not display actual run times. Instead, the next_run_date and next_run_time in the result set are for projected run date and time values derived from the schedule associated with a job. When there is no schedule for a job, then there are no next_run_date and next_run_time values because there is no schedule from which to derive the values. As a consequence, a row for the job does not appear in the sysjobschedules table.

messages

The next script shows how to join concurrently the sysjobschedules table and the sysjobsteps table to the sysjobs table. The sysjobsteps table joins with the sysjobs table by an INNER JOIN based on job_id values in both tables. However, the sysjobschedules table must use a LEFT JOIN to match rows with the sysjobs table based on job_id. This is because not all rows in the sysjobs table have a schedule associated with them. Using an INNER JOIN between sysjobs and sysjobschedules would cause jobs without a schedule to be missing from the result set.

-- list of jobs with name, steps, last run date/time, next_run_date/time
SELECT 
name
,CONVERT(VARCHAR(16), date_created, 120) date_created
,sysjobsteps.step_id
,sysjobsteps.step_name
,LEFT(CAST(sysjobsteps.last_run_date AS VARCHAR),4)+ '-'
+SUBSTRING(CAST(sysjobsteps.last_run_date AS VARCHAR),5,2)+'-'
+SUBSTRING(CAST(sysjobsteps.last_run_date AS VARCHAR),7,2) last_run_date
,
CASE 
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 6  
  THEN SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,2) 
    +':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),3,2)
    +':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),5,2)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 5
  THEN '0' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,1) 
    +':'+SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),2,2)
    +':'+SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),4,2)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 4
  THEN '00:' 
    + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,2)
    +':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),3,2)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 3
  THEN '00:' 
    +'0' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),1,1)
    +':' + SUBSTRING(CAST(sysjobsteps.last_run_time AS VARCHAR),2,2)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 2  THEN '00:00:' + CAST(sysjobsteps.last_run_time AS VARCHAR)
 WHEN LEN(CAST(sysjobsteps.last_run_time AS VARCHAR)) = 1  THEN '00:00:' + '0'+ CAST(sysjobsteps.last_run_time AS VARCHAR)
END last_run_time
,LEFT(CAST(sysjobschedules.next_run_date AS VARCHAR),4)+ '-'
+SUBSTRING(CAST(sysjobschedules.next_run_date AS VARCHAR),5,2)+'-'
+SUBSTRING(CAST(sysjobschedules.next_run_date AS VARCHAR),7,2) next_run_date
,
CASE 
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 6  
  THEN SUBSTRING(CAST(next_run_time AS VARCHAR),1,2) 
    +':' + SUBSTRING(CAST(next_run_time AS VARCHAR),3,2)
    +':' + SUBSTRING(CAST(next_run_time AS VARCHAR),5,2)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 5
  THEN '0' + SUBSTRING(CAST(next_run_time AS VARCHAR),1,1) 
    +':'+SUBSTRING(CAST(next_run_time AS VARCHAR),2,2)
    +':'+SUBSTRING(CAST(next_run_time AS VARCHAR),4,2)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 4
  THEN '00:' 
    + SUBSTRING(CAST(next_run_time AS VARCHAR),1,2)
    +':' + SUBSTRING(CAST(next_run_time AS VARCHAR),3,2)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 3
  THEN '00:' 
    +'0' + SUBSTRING(CAST(next_run_time AS VARCHAR),1,1)
    +':' + SUBSTRING(CAST(next_run_time AS VARCHAR),2,2)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 2  THEN '00:00:' + CAST(next_run_time AS VARCHAR)
 WHEN LEN(CAST(next_run_time AS VARCHAR)) = 1  THEN '00:00:' + '0'+ CAST(next_run_time AS VARCHAR)
END next_run_time
FROM msdb.dbo.sysjobs
LEFT JOIN msdb.dbo.sysjobschedules
ON sysjobs.job_id = sysjobschedules.job_id
INNER JOIN msdb.dbo.sysjobsteps
ON sysjobs.job_id = sysjobsteps.job_id
ORDER BY sysjobs.job_id, sysjobsteps.step_id

The next screen shot displays the result set from the preceding query. As you can see, the result set contains data based on columns from three tables. Name and date_created are from the sysjobs table. Step_id, step_name, last_run_date, and last_run_time are from the sysjobsteps table. Next_run_date and next_run_time are from the sysjobschedules table. This result set points to the kind of documentation breadth that you can provide from joining multiple tables with descriptive information about SQL Server Agent jobs. Critically for this particular demonstration, the query and its result set illustrate how to display next_run_date and next_run_time along with other properties when preparing an inventory of SQL Server Agent jobs.

erase
Next Steps
  • This tip shows how to develop an inventory of jobs on a SQL Server Agent installation. The most obvious next step is to try the scripts on your SQL Server Agent installation. For your convenience, the scripts are available as a download file from the MSSQLTips.com site. Study the result sets and share the outcome with colleagues and supervisors. You might just discover something interesting and actionable about the jobs on your SQL Server Agent.
  • The job inventory not only lists individual jobs in SQL Server Agent, but it also provides helpful information for understanding when the jobs were first created, last modified, and last run along with when jobs will next run. This tip covers a selection of columns from just three tables. It is possible to drill more deeply into the jobs on a SQL Server Agent by including additional columns from these tables as well as other tables not covered in this tip. This kind of exploration can be useful for discovering jobs that are candidates for deletion because they are no longer needed on a SQL Server Agent.
  • You can grow your understanding of the queries and result sets presented in this tip by reviewing the original pair of articles for creating jobs like those inventoried in this tip. Recall that one of these articles is for getting started at creating single-step jobs and another article is for creating multistep and dynamic jobs.
  • Finally, MSSQLTips.com has scores of tips on how to take advantage SQL Server Agent. Here's the link for the page providing a list of these tips. I encourage you to grow your understanding of SQL Server Agent by perusing the list and reading as many tips as seem relevant to your interests and needs.


Last Updated: 2017-07-21


get scripts

next tip button



About the author
MSSQLTips author Rick Dobson Rick Dobson is a Microsoft Certified Technical Specialist and well accomplished SQL Server and Access author.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, September 12, 2018 - 5:51:44 PM - Loralei Back To Top

I do not have the ability to view the definition in new job location. I AM the DBA but cant view stored procedures.

Is there a way or a website that I can see the actualy code to some of the system sprocs. I also can not actually execute system sprocs so I am needing a way to run the code of the sproc to get info. The main one i need right now is sp_help_in_jobs_schedule

 

I have been a DBA / Dev for 20 years and never worked anywhere where the SQL DBA cannot view the definition for code! And they also forbid SQL DBA from accessing SQL Server AGENT... I can not see, create or execute jobs! How weird is that?


Monday, July 24, 2017 - 5:39:07 AM - Toni Back To Top

 Thank you.  This is a very helpful query.

 


Saturday, July 22, 2017 - 8:49:03 AM - Rick Dobson Back To Top

I want to remind you initially thta the msdb and master databases are two different system databases, and the tables used in this tip are from the msdb database -- not the master database.

 

Next, I want to remind you of the value of backups.  See this reference for restoring a master database from a backup copy: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-the-master-database-transact-sql

 

Finally, I want to refer you to this reference which tells you what to try when there is no backup  of the master database: https://www.mssqltips.com/sqlservertip/3266/restoring-the-sql-server-master-database-even-without-a-backup/

 

I hope this reply is helpful.

 

Rick Dobson


Friday, July 21, 2017 - 9:30:00 PM - venki.m Back To Top

 In case of master database corruption., how to rebuild an instance.? 

 


Learn more about SQL Server tools