Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jholovacs
Posting Yak Master
163 Posts |
 Posted - 2008-11-12 : 16:24:29
|
| we're trying to grab some data from msdb.dbo.sysjobhistory linked to msdb.dbo.sysjobs... looking to create an interactive drilldown report on individual steps in the jobs.Ran across a strange issue.While the msdb.dbo.sysjobhistory table shows all the steps and their associated output, and the associated job, it would make sense if we could tie the steps to a specific job run, but we can't seem to find any referential information of any sort for that.Anyone have any ideas? Ultimately, we need to identify a job run, and see the output for each step of that job run.___________________________Geek At Large |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-12 : 16:30:07
|
| You need to see in msdb..sysjobsteps. |
 |
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2008-11-12 : 16:34:25
|
| sysjobsteps does not reference a given job run, just the last job run; and even then, it only loosely references it by last_run_date and time. I'm looking for a genuine reference that assigns the instances in the syskobhistory table to a specific job run, and most likely not the last one.___________________________Geek At Large |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-12 : 16:59:05
|
Is this what you want?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2008-11-12 : 17:06:00
|
| I found the answer.... and boy does it suck:You find the job_id, start date/ time (which are *&%*% INTEGER columns, riddle me that!), and duration from msdb.dbo.sp_help_jobhistory, convert the start date and time to a DATETIME, add the duration, convert the resultant DATETIME back into INTEGER components (&*^%$&^%$!!!!) and rerun the msdb.dbo.sp_help_jobhistory with end_run parameters. this will return a pretty set of steps from the dbo.sysjobhistory table in the right order that you can then look in on, the idea being that a given job will not be running more than once at any given moment.I think it is the most screwed up logging I have ever seen, and I've seen some dumb stuff. It looks like I will have to build my own job_run table and reference the instance_id's to use in my reporting application. What a (&*^*( &*^*&% pile of (*^(^....___________________________Geek At Large |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2008-11-13 : 02:53:41
|
| PESO WHEN YOU R GOING TO HIT 20000 POSTS |
|
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2008-11-13 : 08:55:21
|
| That is not what I was looking for. The job run may not be scheduled; it may be run manually. I needed to create a drilldown of each job run, not each scheduled job run, as I'm pretty sure I clearly stated above. Also, 400+ lines of SQL code to get the data that should be available in one simple query is a bit much, and I can't think why anyone would disagree.I would appreciate if people withheld the condescension until the solution they present actually meets the stated requirements.Secondly, my surprise is that it is so ridiculously cumbersome. Turning dates and times into two seperate columns of formatted integers?!? There is absolutely no advantage to that and a long string of disadvantages. The fact that there is no referential key information about a specific job run ANYWHERE? This strikes me as a rather large and obvious oversight byt the developers at Microsoft.___________________________Geek At Large |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-13 : 09:37:14
|
Why cast back and forth?This query produces the exact same output as the sp_help_jobhistory stored procedure.SELECT job_id, job_name, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, serverFROM ( SELECT sj.job_id, sj.name AS job_name, sjh.run_status, sjh.run_date, sjh.run_time, sjh.run_duration, NULLIF(sjh.operator_id_emailed, 0) AS operator_id_emailed, NULLIF(sjh.operator_id_netsent, 0) AS operator_id_netsent, NULLIF(sjh.operator_id_paged, 0) AS operator_id_paged, sjh.retries_attempted, server, STR(sjh.run_date, 8) + ' ' + STR(sjh.run_time, 6) AS theDateTime, sjh.instance_id FROM msdb..sysjobhistory AS sjh INNER JOIN msdb..sysjobs AS sj ON sj.job_id = sjh.job_id ) AS dWHERE theDateTime >= '20081101 150000' AND theDateTime < '20081102 120000'ORDER BY instance_id desc
E 12°55'05.63"N 56°04'39.26" |
|
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2008-11-13 : 09:51:29
|
| Definitely closer to what I wanted. This will get me to the point where I can identify step history by job_run, but as you say, this query provides the same info as the sp_help_jobhistory proc, so for simplification's sake why not use that instead?This will still require CASTing, however; you simplified it by formatting the datetime data as a string, but the search argument will not be passed as a string, it will be passed a start time and a duration, in seconds, which will then need to compute the end time. Unless you manually want to deal with minute, hour, day (month? year?) wrapping logic in your query the fastest way will still be to convert it to a DATETIME, perform the date functions for adding the duration, and then reconverting back to use the argument in the WHERE clause.___________________________Geek At Large |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-13 : 10:09:07
|
Because you can't easily use the resultset from the stored procedure?You need to insert it into a temporary table (or table varuable) first?Where is my CASTing?Perhaps you should begin to explaining the requirements of your code?Is this going to be a stored procedure?What parameters are user supplying?What output do you expect?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
jholovacs
Posting Yak Master
163 Posts |
Posted - 2008-11-13 : 10:52:45
|
The resultset from the sp has most of the info I require. I also need to supply server information, as the job histories for multiple servers will be consolidated on one central reporting server, but that is unimportant for this exercise. The problem is that the sp gives me too much information (results from multiple job runs) when I need a specific subset, i.e., only but all of the step history from a specific job run.Ultimately in lieu of a temp table I intend on storing this information for multiple servers in a table on a reporting server. I think it makes more sense that way, for reporting purposes, but again I believe that is unimportant to the discussion.You CAST the integers as strings in the following line:STR(sjh.run_date, 8) + ' ' + STR(sjh.run_time, 6) AS theDateTime ...and keep in mind, you passed the WHERE clause arguments as strings too. Nowhere else in the tables do these values show as strings, so it implies that somewhere there will be a CAST to get that data in the format you specified. All of that does not answer the fact that you arbitrarily selected an end date and end time; this information is not available anywhere in the tables as you have used; as far as I can tell it needs to be calculated from the start date, start time, and duration, as this is the only way that end date and end time can be derived. My previous response to you indicated that I would not be able to avoid CASTing with your query, and I maintain that to be accurate.This is a drilldown-type interactive job run report. There will be lists of jobs from multiple servers, with an overview, and the user will need to drill down on a specific job run to get details. So first, I identify job runs, which is fairly straightforward, by looking in the msdb.dbo.sysjobhistory table for all records with a step_id of 0.I should mention that we will be adding information to the job data in a property table regarding performance issues, disk and network I/O, and other flags that will allow us to trend job execution and identify bottlenecks in our distributed architecture, but I do not believe this will be helpful to the conversation at this point.Once a user identifies a job run he or she wants to investigate more closely, I need to identify the step details in a specific job run. The only way to uniquely identify a complete job run (that I can see) is by job_id, start time, and duration (or stop time). The only way I see to get the step information for a specific job run is from the msdb.dbo.sysjobhistory, filtering on the step_id of 0, which will give the run date, run time, and run duration, then using this information as a base, calculate all the steps that fall into the date and time ranges for that particular job_id, and arrange them in order of execution.Identifying the steps in that particular job run is the core problem, as stated in my original post. All I want to do is programatically identify all the steps that belong to a specific job run. Not by date, not by time, but by job run. if I have to use the start and finish times to calculate it, so be it, but it's really ancillary information.___________________________Geek At Large |
|
|
|
|
|
|
|
|
job analysis