Script to find SQL Job activity details

Posted by Vidhya Sagar on September 17, 2009 Leave a comment (27) Go to comments

I’ve 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. I’ve tried sp_help_jobactivity for SQL2K5 \ SQL2k8, however it doesn’t provide the columns needed. So I’ve 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 won’t 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.

Syntax

sqlcmd –E –S Servername –i”file 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 –i”C:\temp\job_details.sql” > “C:\temp\job_activity_details.txt”

sqlcmd –E –S SAGARPC\SQL2008 –i”C:\temp\job_details.sql” >> “C:\temp\job_activity_details.txt”

I’ve saved the SQL script file to C:\temp\job_details.sql path and I’ve 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:\temp\job_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, please write it in our discussion board.

Rating: 3.9/5 (8 votes cast)

Rating: +8 (from 8 votes)

Script to find SQL Job activity details, 3.9 out of 5 based on 8 ratings

ScriptsAdministration, Scripts

← 10 Minutes to configure Reporting Services 2008

Running Adhoc queries against multiple servers using SSMS 2008 →

27 Comments.

Utsav September 23, 2009 at 6:36 pm

Sounds good!

But on sql 2000 i got following error -

Server: Msg 195, Level 15, State 10, Line 3
‘SERVERPROPERTY’ is not a recognized function name.
Server: Msg 195, Level 15, State 1, Line 7
‘SERVERPROPERTY’ is not a recognized function name.
Server: Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword ‘END’.
Server: Msg 195, Level 15, State 1, Line 47
‘SERVERPROPERTY’ is not a recognized function name.
Vidhya Sagar September 23, 2009 at 9:06 pm

Utsav,
This query should work fine on SQL 2000 since I’ve tested this and I’m using it in my environment. I hope you might be executing this query against SQL 7, in SQL 7 we don’t have SERVERPROPERTY function. Hence I request you to run the below command to verify the version.

SELECT @@version
DotNetShoutout - trackback on September 30, 2009 at 12:54 pm
Nagesh October 13, 2009 at 5:39 pm

Thanks to have a good script.
Nagesh October 14, 2009 at 12:58 pm

Hi,

Please let me know how I can get the procedure name(command),databsename and owner name along with the above output.
I got the details through exec sp_help_job ‘jobid’ but how can I store them in a table or add to the exisiting query.

Thanks,
Nagesh
Vidhya Sagar October 14, 2009 at 1:42 pm

Hi Nagesh,

Can we have the discussion @ FORUMS section. It will be easier for me to follow up.

Thanks
Anil K Rao October 14, 2009 at 3:58 pm

Its nice script
Gautam November 3, 2009 at 3:39 pm

Thanks a lot man..
solved my purpose end to end..
thanks a lot..
coicenichorma January 2, 2010 at 8:11 am

Thx for the post!!
Sachin Pakale October 7, 2010 at 2:54 pm

Thank you so much. It made my work easy
sarang March 7, 2011 at 11:20 pm

can any 1 tell me how to get the informatoin of daily,weekly job in SQL SERVER 2000
Rajesh Kasturi March 31, 2011 at 10:39 am

Hi,

This is a good script, Thank you so much.
Please modify the below command at the end
drop table ###jobdetails to drop table ##jobdetails
Sridhar April 30, 2011 at 11:30 am

it would be good if you include the owner of the job as well
Vidhya Sagar June 7, 2011 at 9:01 pm

@sridhar you can use the script below
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,SUSER_SNAME(j.owner_sid) as Job_Owner,
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,owner,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,owner,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
END
kunal November 2, 2011 at 3:23 pm

a lot thanks for such a wonderful script
Mafe Santiago May 24, 2012 at 11:21 am

Hello,
I’m just new with sql and i need script that can collect sql jobs info. your script it running. but when i tried to run it via sqlcmd the output has mess and it also has the code on it.
Vidhya Sagar June 1, 2012 at 2:21 pm

You need to save this query to a text file and point this query file using -i parameter in SQLCMD utility
anon June 21, 2012 at 10:01 pm

Thank you this is super useful!
Ahsan Habib July 18, 2012 at 12:04 am

Hi Vidhya,
Wonderful script. I have added current_execution_status column to the script and it is working fine. However, this field is always showing 4 (idle)for all the jobs even for the jobs that are currently executing (current_execution_status should be 1). Any idea what might be the issue?
Thanks
Dibya July 31, 2012 at 12:07 pm

Nice Query. Solved my problem.
Vidhya Sagar August 6, 2012 at 4:13 pm

I think its not picking properly the job name, some problem with the modified query.
lavanya November 26, 2012 at 4:04 pm

hi,the script is not working in SQL2000. please check the error.

Msg 14, Level 16, State 1, Line 1
[DBNETLIB][ConnectionOpen (Invalid Instance()).]Invalid connection.
Lisa January 21, 2013 at 7:52 pm

Hi

I get the following error and cannot see the table in the list of system tables for msdb on our sql 2000 db (it works fine on the 2005s):

Msg 15009, Level 16, State 1
The object ‘sysjobactivity’ does not exist in database ‘msdb’.

Any ideas?
sam February 6, 2013 at 3:52 am

This script is great but recently we have shifted to sql sentry and now the script does not work as the msdb.dbo.sysjobhistory table will not have the job data and so the script is returning a null value can you please help me with this.
Vidhya Sagar February 6, 2013 at 10:30 pm

It’s seems the script is not able to connect to the server properly. It looks like your sysservers table has some invalid entry.
Vidhya Sagar February 6, 2013 at 10:36 pm

I do have separate script for sql server 2000, you can use that.
Vidhya Sagar February 6, 2013 at 10:38 pm

Im not sure about SQL Sentry, let me give a try and then let you know the details