September 18, 2009 at 10:00 am
Hi,
Trying to understand how sysjobs, sysjobhistory, sysjobsteps, and sysjobactivity relate each and can be utilized to trace back the performance of jobs in regards of when, how long, and if successfully executed or not has brought me to the point to ask for assistance 🙂
The purpose of this post is to get an insight of how admins can apply these tables to retrieve a current snapshot of running jobs historical analysis of how jobs have been performing.
Thanks
September 18, 2009 at 12:11 pm
Here is a sort of starter script to show a little bit of the interaction. This will give a quick overview of some of the properties of your jobs. If you look into it and compare with the tables, I'm sure you will see how the tables link together.
Declare @Session_IDInt
Select @Session_ID = max(session_id) from sysjobactivity
Select p.name as OwnerName, j.name as JobName, count(s.step_id) as NumSteps, j.date_created as CreatedDate
,Case j.enabled
When 1
Then 'Enabled'
Else
'Disabled'
End As IsEnabled
,a.start_execution_date as LastRunDate, a.Next_Scheduled_Run_Date as NextRunDate
,Case h.run_status
When 0
Then 'Failed'
When 1
Then 'Succeeded'
When 2
Then 'Retry'
When 3
Then 'Canceled'
When 4
Then 'In Progress'
End As LastRunStatus
,h.run_duration as RunTime,h.message As MessageText
,Case IsNull(h.instance_id,0)
When 0
Then 'True'
Else
'False'
End As JobHasHistory
From sysjobs j
Inner Join master.sys.server_principals p
on p.sid = j.owner_sid
Inner Join sysjobactivity a
On a.job_id = j.job_id
And a.session_id = @Session_ID
Inner Join sysjobsteps s
On s.job_id = j.job_id
Left Outer Join sysjobhistory h
On a.job_history_id = h.instance_id
Group By s.job_id, p.name,j.name,j.date_created,j.enabled,a.start_execution_date,a.Next_Scheduled_Run_Date,h.run_status,h.run_duration,h.message,h.instance_id
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 18, 2009 at 2:38 pm
Thanks for the Script
Will analyze => understand => and conquer 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply