Determine SQL Server JOB, activity, status, run details


 

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.enabledWHEN 1 THEN‘Enabled’Else‘Disabled’ENDAS 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’ENDAS‘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 LEFTJOIN 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=(SELECTMAX(session_id)from msdb.dbo.sysjobactivity)ORDERBY 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 LEFTJOIN 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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: