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 doesnt 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


--Checking for SQL Server verion
IF CONVERT(tinyint,(SUBSTRING(CONVERT(CHAR(1),SERVERPROPERTY('productversion')),1,1))) <> 8
---This is for SQL 2k5 and SQL2k8 servers
SELECT Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName, 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,
CONVERT(VARCHAR(500),jh.message) AS step_description
(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
--This is for SQL2k servers
--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

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


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.


sqlcmd E S Servername ifile path > Output Path


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

Leave a Reply

Leave a Reply

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