Archive for November, 2008

Query to Check status of Sqlserver Job

Posted: November 18, 2008 in Sqlserver
To Check the Status of any Sqlserver Job.
–Run the below query on MSDB database
Use MSDB
Select
jh.instance_id
,j.name as jobname
,jh.step_id
    ,jh.step_name as JobStepName
,Case jh.run_status
When  0 THEN ‘Failed’
When 1 then ‘Success’
when 2 then ‘Retry’
when 3 then ‘Cancelled’
when 4 then ‘InProgress’
End as runstatus
,CAST (Left(cast(run_date as varchar ),4) + ‘/’ + 
substring(cast(run_date as varchar ), 5,2) + ‘/’ + 
Right(cast(run_date as varchar ), 2) + ‘ ‘ + 
cast( ((run_time/10000) %100) as varchar ) + ‘:’ + 
cast( ((run_time/100) %100) as varchar ) + ‘:’ +
cast( (run_time %100) as varchar ) as datetime) as RunTime,
run_duration 
From
msdb..sysjobs j
join
msdb..sysjobhistory jh
on 
j.job_id = jh.job_id
and
j.name = ‘Databasebackup_Job’   —Filter by Job name