Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2000 - How long has a job (all steps inclusive) been running? Expand / Collapse
Author
Message
Posted Tuesday, November 26, 2013 10:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 11:19 AM
Points: 105, Visits: 191
So I've scoured the web and it seems everything I find will alert really well on just one job step that runs over a predetermined time - But nothing for the entire job.

You can look at the History view while a job is running and watch it update total run-time so I know it's possible - I am just not smart enough to figure it out with the myriad of solutions I've tried to customize and cob together.

So - Anyone out there able to assist? Looking to query either granularly or globally any jobs that last more than 3-4 hours and then shoot out an alert via XP_Sendmail.

Thanks in advance!


-- Wally
-- Reluctant DBA
Post #1517751
Posted Tuesday, November 26, 2013 12:38 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 227, Visits: 2,165
Start with the sysjobactivity table, and you should be able to come up with something that fits your needs.
Post #1517802
Posted Tuesday, November 26, 2013 1:21 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 11:19 AM
Points: 105, Visits: 191
That is a 2005+ DMV... Hence my dilemma...

-- Wally
-- Reluctant DBA
Post #1517812
Posted Tuesday, November 26, 2013 1:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 12,905, Visits: 32,158
lptech (11/26/2013)
Start with the sysjobactivity table, and you should be able to come up with something that fits your needs.

I don't have a 2000 server to test agaisnt, but assuming the table is still the same, i *think* this will give you teh results you are looking for?

SELECT
DATEDIFF(minute,start_execution_date,getdate()) AS ElapsedMinutes,
*
FROM msdb.dbo.sysjobactivity
WHERE start_execution_date IS NOT NULL
AND stop_execution_date IS NULL




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1517813
Posted Tuesday, November 26, 2013 1:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 11:19 AM
Points: 105, Visits: 191
Lowell (11/26/2013)
lptech (11/26/2013)
Start with the sysjobactivity table, and you should be able to come up with something that fits your needs.

I don't have a 2000 server to test agaisnt, but assuming the table is still the same, i *think* this will give you teh results you are looking for?

SELECT
DATEDIFF(minute,start_execution_date,getdate()) AS ElapsedMinutes,
*
FROM msdb.dbo.sysjobactivity
WHERE start_execution_date IS NOT NULL
AND stop_execution_date IS NULL




Yes alas - No msdb.dbo.sysjobactivity table in 2000...

You have - dbo.xp_sqlagent_enum_jobs
msdb.dbo.sysjobs

And the sysjob history table - Pulling all those together is quite an exercise in humility for me...


-- Wally
-- Reluctant DBA
Post #1517819
Posted Tuesday, November 26, 2013 1:51 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 12,905, Visits: 32,158
ok, flexing my Google-Fu a little bit, i found this article that might help; it claims to find long running jobs in 2000-2008 versions:
http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AgentLongRunning


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1517820
Posted Tuesday, November 26, 2013 2:00 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 11:19 AM
Points: 105, Visits: 191
Yeah that's one I've played with but it can only detects the run time of the currently running step.

For instance I ran it just now on one of our load jobs that has been going for almost 4 hours... It only alerts at 45 minutes and below because the current step has been running about 40 minutes. Select 60 minutes and a null return...

But of course in the Job History View you can plainly see a 4+ hour total runtime.

There is some ninja magic out there somewhere that this reluctant DBA can't whip out to look at all the job steps completed and running for a currently active job and sum them up...


-- Wally
-- Reluctant DBA
Post #1517823
Posted Tuesday, November 26, 2013 2:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 227, Visits: 2,165
Whoops, didn't see the 2000 part first time around.
Post #1517826
Posted Thursday, December 12, 2013 9:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, October 13, 2014 4:10 AM
Points: 442, Visits: 10,949
It is possible if you use xp_sqlagent_enum_jobs and then convert the Job ID value to a string value using the fn_varbintohexstr function and then find a row in sysprocesses where the program name contains that value. The login_time value in sysprocesses is then the start time of the job.

For example,


CREATE TABLE #jobs
(
Job_ID uniqueidentifier,
Last_Run_Date int,
Last_Run_Time int,
Next_Run_Date int,
Next_Run_Time int,
Next_Run_Schedule_ID int,
Requested_To_Run int,
Request_Source int,
Request_Source_ID varchar(100),
Running int,
Current_Step int,
Current_Retry_Attempt int,
State int
)

INSERT INTO #jobs EXECUTE master.dbo.xp_sqlagent_enum_jobs 1 ,''
---select * from #jobs where Running =1

SELECT login_time, program_name FROM sysprocesses WHERE lower(program_name) LIKE
(SELECT '%' + master.dbo.fn_varbintohexstr(#jobs.Job_ID) + '%' FROM #jobs
WHERE Running=1)

DROP TABLE #jobs


Post #1522374
Posted Thursday, December 12, 2013 9:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 11:19 AM
Points: 105, Visits: 191
Thanks for the input tripleAxe - I've been back and forth numerous times on many revisions - I'll poke through yours and see if I get some new insight on getting that magical cumulative time.



-- Wally
-- Reluctant DBA
Post #1522390
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse