March 25, 2009 at 9:05 am
Not sure how to approach this, any help is greatly appreciated. I have a remote SQL server (production), and a local SQL server (reporting). The production server runs some nightly jobs, then when it's done, the last action is to start some reporting jobs on the local reporting SQL server.
Both can take several hours, and the reporting server data isn't available to users until the jobs are complete. I'm trying to put together an alert to let people know when the data is available so they can run their report.
From a previous post I know that I can query the sysjob history and get the job name and the duration:
SELECT
j.NAME AS job_name
,CASE run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In Progress'
ELSE ''
END AS run_description
-- ,message
,h.run_date
,h.run_time
,h.run_duration
FROM
msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id AND h.step_id = 0
What I would like to do is to somehow get the average duration of the 10 jobs in the history, and the current day's job and compare them. So that if the average job duration is say 2 hours, and the current day's job started 1 hour ago, then the output would be "data will be available in approx. 1 hour"
Any help on figuring this out is greatly appreciated, thanks!
March 26, 2009 at 6:03 am
Hi,
I have written a procedure that will probably do the job. I have been unable to test it properly as i haven't got any jobs running during the day so it may need some adaption.It should give you a good base though.
You will have to enter in the job name, and will only currently run on one job at a time.
see if you think it gives you what you need :
--------------------------------------
Create PROC CurrentJobProgressProc
@JobName VARCHAR(100)
AS
DECLARE @Error INT
DECLARE @Message VARCHAR(100)
DECLARE @Enddate DATETIME
SET @Error = 0
-------Gather all useful data on past successful jobs under name in param--
--splitting out date and time info from fields so that we can find average--------
SELECT
IDENTITY(INT,1,1) AS Idcol,
SUBSTRING(CAST(run_date AS VARCHAR(100)),1,4) AS RunDate_year,
SUBSTRING(CAST(run_date AS VARCHAR(100)),5,2) AS RunDate_Month,
SUBSTRING(CAST(run_date AS VARCHAR(100)),7,2) AS RunDate_Day,
HoursRan = CASE
WHEN LEN(run_duration) = 5
THEN '0' + SUBSTRING(CAST(run_duration AS VARCHAR(100)),1,1)
WHEN LEN(run_duration) = 6
THEN SUBSTRING(CAST(run_duration AS VARCHAR(100)),1,2)
ELSE 0
END,
MinutesRan = CASE
WHEN LEN(run_duration) = 3
THEN '0' + SUBSTRING(CAST(run_duration AS VARCHAR(100)),1,1)
WHEN LEN(run_duration) = 4
THEN SUBSTRING(CAST(run_duration AS VARCHAR(100)),1,2)
ELSE 0
END,
SecondsRan = CASE
WHEN LEN(run_duration) = 1
THEN '0' + SUBSTRING(CAST(run_duration AS VARCHAR(100)),1,1)
WHEN LEN(run_duration) = 2
THEN SUBSTRING(CAST(run_duration AS VARCHAR(100)),1,2)
ELSE 0
END,
run_duration,
Start_time = CASE
WHEN LEN(run_time) = 6 then
SUBSTRING (CAST(run_time AS VARCHAR(10)),1,2) + ':' +
SUBSTRING (CAST(run_time AS VARCHAR(10)),3,2) + ':' +
SUBSTRING (CAST(run_time AS VARCHAR(10)),5,2)
WHEN LEN(run_time) = 5 then
'0' + SUBSTRING (CAST(run_time AS VARCHAR(10)),1,1) + ':' +
SUBSTRING (CAST(run_time AS VARCHAR(10)),2,2) + ':' +
SUBSTRING (CAST(run_time AS VARCHAR(10)),4,2)
WHEN LEN(run_time) = 1 THEN
'0' + SUBSTRING (CAST(run_time AS VARCHAR(10)),1,1) + ':' + '00:00'
END,
run_status
INTO #tempa
FROM
msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id AND h.step_id = 0
WHERE j.NAME = @JobName
AND run_status = 1
------------Create new table with End Time column---------
SELECT CONVERT(DATETIME,RunDate_year + '-' +
RunDate_Month + '-' +
RunDate_Day + ' ' +
Start_time) Starttime,
CONVERT(DATETIME,RunDate_year + '-' +
RunDate_Month + '-' +
RunDate_Day + ' ' +
Start_time) EndDate , *
INTO #tempb
FROM #tempa
------------------------------Find averages for completed jobs------------------
SELECT AVG(hoursran)AVGHours,AVG(MinutesRan)AVGMin,AVG(secondsRan)AVGSec
INTO #tempc
FROM #tempb
-------------Look to see if that job is currently in progress----------
SELECT
Start_time = CASE
WHEN LEN(run_time) = 6 then
SUBSTRING (CAST(run_time AS VARCHAR(10)),1,2) + ':' +
SUBSTRING (CAST(run_time AS VARCHAR(10)),3,2) + ':' +
SUBSTRING (CAST(run_time AS VARCHAR(10)),5,2)
WHEN LEN(run_time) = 5 then
'0' + SUBSTRING (CAST(run_time AS VARCHAR(10)),1,1) + ':' +
SUBSTRING (CAST(run_time AS VARCHAR(10)),2,2) + ':' +
SUBSTRING (CAST(run_time AS VARCHAR(10)),4,2)
WHEN LEN(run_time) = 1 THEN
'0' + SUBSTRING (CAST(run_time AS VARCHAR(10)),1,1) + ':' + '00:00'
END,
IDENTITY(INT,1,1) AS Idcol,
SUBSTRING(CAST(run_date AS VARCHAR(100)),1,4) AS RunDate_year,
SUBSTRING(CAST(run_date AS VARCHAR(100)),5,2) AS RunDate_Month,
SUBSTRING(CAST(run_date AS VARCHAR(100)),7,2) AS RunDate_Day
INTO #tempd
FROM
msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id AND h.step_id = 0
WHERE j.NAME = @JobName
AND run_status = 4
------------------Add in start and end date column--------------
SELECT CONVERT(DATETIME,RunDate_year + '-' +
RunDate_Month + '-' +
RunDate_Day + ' ' +
Start_time) Starttime,
CONVERT(DATETIME,RunDate_year + '-' +
RunDate_Month + '-' +
RunDate_Day + ' ' +
Start_time) EndDate , *
INTO #tempe
FROM #tempd
--------------If its not running SET ERROR -----------
IF(SELECT COUNT(*) FROM #tempd) = 0
BEGIN
PRINT 's'
SET @Error = 1
SET @Message = 'Job is currently not running'
END
---------Otherwise update table with job in progress with an approximate end time---------
ELSE
UPDATE #tempe
SET Enddate = (SELECT DATEADD(hh,AVGHours,Starttime) FROM #tempc)
FROM #tempe
UPDATE #tempe
SET Enddate = (SELECT DATEADD(mi,AVGMin,Starttime) FROM #tempc)
FROM #tempe
UPDATE #tempe
SET Enddate = (SELECT DATEADD(ss,AVGSec,Starttime) FROM #tempc)
FROM #tempe
-------Update Param with Approx End Date---------
SET @Enddate = (SELECT EndDate FROM #tempe)
-------If no Error has been returned return approx End Date / time-------
IF @Error = 0
BEGIN
SET @Message = 'Job will finish At Approx Date / Time : ' + CAST(@EndDate AS VARCHAR(100))
SELECT @Message Message
END
ELSE
SELECT @Error Error, @Message MESSAGE
/*
EXEC CurrentJobProgressProc 'HEFT Process Offline Transactions'
*/
let me know if you have any questions
Thanks
Matt
March 26, 2009 at 6:45 am
Hello,
what exactly is your problem?
You know where data about the jobs is stored so you should be able to calculate the average.
SELECT job.[name] as Job, his.step_id, his.step_name,
MAX(his.run_duration) as Max_duration,
AVG(his.run_duration) as Avg_duration
from msdb..sysjobs job
JOIN msdb..sysjobhistory his ON his.job_id = job.job_id
WHERE his.run_status = 1 and his.step_id = 0
group by job.[name], his.step_id, his.step_name
Some data about jobs can be found using system stored procedure : exec msdb.dbo.sp_help_job. You can insert the result into a temporary table and join to it, if you need.
Please supply more details about what your problem is, then we can try to help you.
March 26, 2009 at 3:21 pm
Thank you both for the replies, I really appreciate it.
I have 2 sql servers, each one runs an SSIS job, one runs JobA first, then when it finishes, it contacts the second server and runs JobB. Both jobs need to be finished before reports can be run against the database accurately.
So on my intranet page where I list my crystal reports, I would like to be able to put a status message indicating whether the current dataset is available yet for reports.
To do this, I need to get the status of both jobs:
If status.jobA = Complete and status.jobB = Complete then
message = Current report data is today's date.
However, if both jobs are not complete, then the message would have to be "Current report data is yesterday's date"
This should be too hard to do, but I was hoping to take it a step further, and figure out how close the jobs are to finishing, and to tell the user to check back in a certain amount of time.
For example, if the average duration time of jobA is 2 hours, and the current jobA begain 1 hour ago, then there's approx. 1 hour left. And if jobB's average duration time is 2 hours, and it hasn't started yet (because jobA starts it when it completes) then there's approximately 3 hours left before the reports are available (1 hour from jobA, then another 2 hours for jobB)
So then my output would be "The current report date is CurrentDate-1, please check back in approximately 3 hours for today's data"
Something like that. Unfortunately, I'm not terribly good at writing advanced procedures like this 🙂 If this is too complex or not adviseable, I guess I can just do an "available/not available" message.
Any help is appreciated.
March 27, 2009 at 2:12 am
Well, you could do that if you really wish, but in my experience the jobs can have very different durations, based on the amount of processed data, fragmentation of indexes etc. I wouldn't hazard any guesses when a job will finish based on previous duration times.
What I would suggest is to add one more step at the end of your jobs, which would then send an e-mail to the respective group of people saying something like "Processing of .... finished, you can view current data in the reports".
If you want to make it nice, prepare some configuration table that will allow to easily set recipients for each such mail (put report name, login and e-mail in the table).
If people come to work and the processing is still running, and go to look at some report, display "Current data still processing, you will be notified by e-mail" ... or "Data still processing. Ask admin if you want to be notified whenever processing ends" (if users are logged in when viewing reports, you should be able to find out from the table whether they already are set as recipients - well, it may depend on the viewing front-end, but you have a fair chance).
If the processing mostly finishes before anyone comes to work, and you want to avoid sending messages daily, you could include a time check and only send the mail if the job finishes after office hours have started. Well, it is up to you, these are just some ideas what could be done.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply