February 6, 2009 at 6:49 am
Hi,
One of the SQL job is started by application team and it is running for the long time. ( Status is executing in the job monitor )
We want to know when the job was started i.e., start date and time.
In SQL 2000 We used to query the sysjobhistory. In SQL 2005 the sysjobhistory shows only completed job details and not one going job execution status.
I have queried sysjobactivity table and sp_help_job @execution_status = 1 but no success.
I will really appreciate if you provide the query to get start date ( run date and time ) of currently executing job.
Regards,
Marimuthu
February 6, 2009 at 7:15 am
SQL 2005 has implemented a new table sysjobactivity. There you find info about all running SQL Agent jobs.
[font="Verdana"]Markus Bohse[/font]
February 6, 2009 at 7:15 am
track down the spid the job is running under using sp_who2 or activity monitor. Check the login_time and last_batch time from master.sysprocesses table.
---------------------------------------------------------------------
February 6, 2009 at 7:41 am
Just did some testing on the sysjobactivity in msdb and figured that the following scripts coudl help you.
It will ist the running jobs, their starting time and how long has been running..
SELECT j.name as Running_Jobs, ja.Start_execution_date As Starting_time,
datediff(ss, ja.Start_execution_date,getdate()) as [Has_been_running(in Sec)]
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs j
ON j.job_id=ja.job_id
WHERE job_history_id is null
AND start_execution_date is NOT NULL
ORDER BY start_execution_date
February 6, 2009 at 8:03 am
have you tried - sp_help_jobactivity @job_name = 'name of your job'
- one of a series in how to skin a cat
---------------------------------------------------------------------
February 6, 2009 at 8:12 am
SQL Server Agent since 7.0 can be scheduled to run for five hours a day five days a week if you are using it for data automation run DTS/SSIS packages the reason the Agent was one of the first features to come in 64bits. The above depends on hardware and setup.
Kind regards,
Gift Peddie
February 8, 2009 at 3:00 am
sp_get_composite_job_info (loading in a bunch of NULLS, and a “1” to indicate running jobs):
Run-->msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL
MJ
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply