Script to find SQL Job activity details

Ive written a script to find the job activity details, this script will give the output similar to job activity monitor in SQL2K5 or SQL2K8. In my working place we had a request to collect the job details from all the servers which includes all version of SQL Server. Ive tried sp_help_jobactivity for SQL2K5 SQL2k8, however it doesn’t provide the columns needed. So Ive written a script to get customized output, this script will also work with SQL2K. The script will fetch the following details

  • Server Name
  • Job Name
  • Job Status
  • Last Run Status
  • Last Run Date and Time
  • Run Duration
  • Next Run Date and Time
  • Message about status of the job

Works with

  • SQL Server 2000
  • SQL Server 2005
  • SQL Server 2008 and
  • SQL Server 2008 R2

Script

SET NOCOUNT ON
--Checking for SQL Server verion
IF CONVERT(tinyint,(SUBSTRING(CONVERT(CHAR(1),SERVERPROPERTY('productversion')),1,1))) <> 8
BEGIN
---This is for SQL 2k5 and SQL2k8 servers
SET NOCOUNT ON
SELECT Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,
j.name AS job_name,
CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,
CASE jh.run_status WHEN 0 THEN 'Error Failed'
                WHEN 1 THEN 'Succeeded'
                WHEN 2 THEN 'Retry'
                WHEN 3 THEN 'Cancelled'
                WHEN 4 THEN 'In Progress' ELSE
                'Status Unknown' END AS 'last_run_status',
ja.run_requested_date as last_run_date,
CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,
ja.next_scheduled_run_date,
CONVERT(VARCHAR(500),jh.message) AS step_description
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id)  from msdb.dbo.sysjobactivity) ORDER BY job_name,job_status
END
ELSE
BEGIN
--This is for SQL2k servers
SET NOCOUNT ON
DECLARE @SQL VARCHAR(5000)
--Getting information from sp_help_job to a temp table
SET @SQL='SELECT job_id,name AS job_name,CASE enabled WHEN 1 THEN ''Enabled'' ELSE ''Disabled'' END AS job_status,
CASE last_run_outcome WHEN 0 THEN ''Error Failed''
                WHEN 1 THEN ''Succeeded''
                WHEN 2 THEN ''Retry''
                WHEN 3 THEN ''Cancelled''
                WHEN 4 THEN ''In Progress'' ELSE
                ''Status Unknown'' END AS  last_run_status,
CASE RTRIM(last_run_date) WHEN 0 THEN 19000101 ELSE last_run_date END last_run_date,
CASE RTRIM(last_run_time) WHEN 0 THEN 235959 ELSE last_run_time END last_run_time, 
CASE RTRIM(next_run_date) WHEN 0 THEN 19000101 ELSE next_run_date END next_run_date, 
CASE RTRIM(next_run_time) WHEN 0 THEN 235959 ELSE next_run_time END next_run_time,
last_run_date AS lrd, last_run_time AS lrt
INTO ##jobdetails
FROM OPENROWSET(''sqloledb'', ''server=(local);trusted_connection=yes'', ''set fmtonly off exec msdb.dbo.sp_help_job'')'
exec (@SQL)
--Merging run date & time format, adding run duration and adding step description
select Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,jd.job_name,jd.job_status,jd.last_run_status,
CONVERT(DATETIME,RTRIM(jd.last_run_date)) +(jd.last_run_time * 9 + jd.last_run_time % 10000 * 6 + jd.last_run_time % 100 * 10) / 216e4 AS last_run_date,
CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,
CONVERT(DATETIME,RTRIM(jd.next_run_date)) +(jd.next_run_time * 9 + jd.next_run_time % 10000 * 6 + jd.next_run_time % 100 * 10) / 216e4 AS next_scheduled_run_date,
CONVERT(VARCHAR(500),jh.message) AS step_description
from (##jobdetails jd  LEFT JOIN  msdb.dbo.sysjobhistory jh ON jd.job_id=jh.job_id AND jd.lrd=jh.run_date AND jd.lrt=jh.run_time) where step_id=0 or step_id is null
order by jd.job_name,jd.job_status
--dropping the temp table
drop table ###jobdetails
END

Download the Script

How to use?

Very simple, just copy the script and paste it in client tools and run it, it will collect the information for you. We are just doing select on tables across msdb database, hence it wont affect your system. Sample output is shown below

job_details

How to use the script in batch file?

You can also use this script in a batch file to get these details from all the servers in your environment. Save the script to a file in a path. Sample batch file command is shown below.

Syntax

sqlcmd E S Servername ifile path > Output Path

Where,

E Windows authentication, if you are connecting with SQL authentication then use U & P parameter

S Provide the server name

i Input SQL file to execute

> Create new output file

>> Append to the existing output file

Sample Command

sqlcmd -E -S SAGARPC -iC:\tempjob_details.sql > C:\tempjob_activity_details.txt

sqlcmd -E -S SAGARPC\SQL2008 -iC:\tempjob_details.sql >> C:\tempjob_activity_details.txt

Ive saved the SQL script file to C:tempjob_details.sql path and Ive used this as input file. You can add N number of servers to the batch file and save the file as *.bat and just execute it, the batch file will collect the information to the file C:tempjob_activity_details.txt. If needed you can add a mail step to send the output to the respective recipients.

If you have any questions or suggestions related to this script write it in comments


Posted

in

by

Comments

7 responses to “Script to find SQL Job activity details”

  1. phani avatar
    phani

    Thanks vidhya sagar you saved me lot of time. i didnt execute complete script just copy the 2k5 and 2k8 script only and executed it works fine.
    Thanks

  2. Plastic Jones avatar
    Plastic Jones

    It ran fine after I removed the superfluous hash mark: ###jobdetails

  3. Stu avatar
    Stu

    The time conversion was a little confusing. It helps to know that somewhere in the guts of SQL Server, they encode it as HHMMSS. Then somehow they convert it to a number, truncate the leading zeros and then publish it as a string.

    So just pad the time component out to a 6 digit length with leading zeros and then parse it as HHMMSS.

    Cheers

  4. William avatar
    William

    Is there a confusion between job status vs job enabled …?:
    CASE j.enabled WHEN 1 THEN ‘Enabled’ Else ‘Disabled’ END AS job_status

    The job_status could be iddle, executing, etc.

    1. VidhyaSagar avatar

      Gotcha will change it

  5. bee avatar
    bee

    dears
    is there any changed is available to your script regarding job status and current running status(executing,Idle), as the last run status will be unknown as the job will be run ,
    is there any way to change your code to be much similar to ‘job activity monitor’ ?
    also if can be possible to add run time after the job has been started

    1. VidhyaSagar avatar

      Thanks for your interest in the script. This script will provide details about past executions and the current execution. Run duration will provide the run time. I believe this script is similar to job activity monitor. Can you please give me more details on what you are looking for so that I can modify the script for you.

Leave a Reply to Stu Cancel reply

Your email address will not be published. Required fields are marked *