View historical information for SQL Server Agent jobs

Original: View history of SQL Server Agent jobs

Not to say well-known, but most people should know that SQL Server Agent operations are stored in the SQLSERVER5 large system database (master/msdb/model/tempdb/resources) in msdb, Because of the long-term operation and variety of agent jobs, it is generally possible to see that the size of msdb is larger than the other libraries combined. This article focuses on how to query the run time of the job and the duration of the run.

As a DBA, periodic checking of jobs is a very important task. This article does not tell too much about depth. Only describes how to query the history of a job. and join in the Books Online is not mentioned, that is, the so-called non-public system functions.

The historical information for job execution is stored in the msdb.dbo.sysjobhistory. But in this table, the explicit way of the date and time column is somewhat unconventional, which leads to the intent of this article. First, let's take a look at the data in the table, where we need to correlate the sysjobs table:

SELECT  j.name as ' JobName ',          run_date,          run_time  from    msdb.dbo.sysjobs J          INNER JOIN Msdb.dbo.sysjobhistory h on j.job_id = h.job_id  WHERE   j.enabled = 1  --only enabled Jobs  ORDER by JobName,          run_date,          run_time DESC

run the above code to get the following result:

Can see run_date This column, although can understand, but is YYYYMMDD such format, use up may be a bit inconvenient. And the run_time is more difficult to use. The 180002 in run_time means: 18:00:02 execution. These non-intuitive data is a pain for DBAs who often need to use it, and, of course, can be converted to a format you like by using String functions. But here is a function that Microsoft does not expose:

MSDB.dbo.agent_datetime (Run_date,run_time)

It will return a more general date format, making it convenient to use and view, and as an undisclosed function, there is little to know about it. You can use the following example:

SELECT  j.name as ' JobName ',         run_date,         run_time,         msdb.dbo.agent_datetime (Run_date, run_time) as ' Rundatetime ' from    msdb.dbo.sysjobs J         INNER joins msdb.dbo.sysjobhistory h on j.job_id = h.job_id WHERE   j.enabled = 1  --only enabled Jobs ORDER by JobName,         

after the result is converted, the following results are obtained:

You can see that after the function has been formatted, the data is already very intuitive. It is important to note that this open function is not introduced until 2005, and 2000 is not. The same effect can be obtained only through string processing.

Now look at the other column, Run_duration, run duration, again, this column is of type int, as well as run_time, not intuitive.

SELECT  j.name as ' JobName ',         run_date,         run_time,         msdb.dbo.agent_datetime (Run_date, run_time) as ' Rundatetime ',         run_duration from    msdb.dbo.sysjobs J         INNER joins msdb.dbo.sysjobhistory h on j.job_id = h.job_id WHERE   j.enabled = 1  --only enabled Jobs ORDER by JobName,         

The results are as follows:

This column of two digits represents just seconds, and 3 digits represent seconds and minutes. It is difficult to see the running time of the job from this point alone. unfavorable to the analysis. Unfortunately, there is no other stored procedure to convert this column, so you need to write your own code, you can use the following code to convert:

SELECT  j.name as ' JobName ',         run_date,         run_time,         msdb.dbo.agent_datetime (Run_date, run_time) as ' Rundatetime ',         run_duration,         ((run_duration/10000 * 3600 + (run_duration/100)% *             + Run_durati On% +/-) as ' rundurationminutes ' from    msdb.dbo.sysjobs J         INNER joins Msdb.dbo.sysjobhistory h on J. job_id = h.job_id WHERE   j.enabled = 1  --only enabled Jobs ORDER by JobName,         

to facilitate the presentation, I've screened a few jobs that lasted longer.

For many ETL jobs, there may be a lot of steps, the following to bring out these steps, it is necessary to associate another table msdb. dbo . sysjobsteps:

SELECT  j.name as ' JobName ',         s.step_id as ' step ',         s.step_name as ' stepname ',         msdb.dbo.agent_datetime ( Run_date, Run_time) as ' Rundatetime ',         ((run_duration/10000 * 3600 + (run_duration/100)% *             + run_d Uration% + +)/() as ' rundurationminutes ' from    msdb.dbo.sysjobs J         INNER JOIN msdb.dbo.sysjobsteps s on J . job_id = s.job_id         INNER JOIN msdb.dbo.sysjobhistory h on s.job_id = h.job_id and                                                s.step_id = h.step_id and                                                h . step_id <> 0 WHERE   j.enabled = 1   --only enabled Jobs ORDER by JobName,         

With this query, you can check which job runs the longest, and then check and optimize. For SQL Server Agent job there are many things to do, due to the theme, it is not possible to complete an article, will be described in the following article.

Checking performance problems from agent jobs is only one of the means of querying performance problems and checking database operation, many database management operations are often not single, but a series of operations synthesized. But to learn a tool, you have the same weapon.

View historical information for SQL Server Agent jobs