Current Job Run Status

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rubens - 04 Jul 2008 20:29 GMT

I am trying to create a query against a SQL 2000 database to see if a job is currently running. I cannot cancel the job that I am monitoring. Shortly after it completes, I need to notify some people and do some additional work. Unfortunately I forgot to change the job to notify me on completion, so I just want to set up a job to run every 10 minutes that will e-mail me when it completes. However, it doesn't appear SQL logs currently running jobs, just the status once it completes.

Can someone tell me how I can find the status of the job when it is currently running? Here is what I have, which returns no records even though the job is running.

select
sj.job_id
,sj.name
,sjh.run_status
,max(sjh.run_date) as run_date
,max(sjh.run_time) as run_time
from msdb.dbo.sysjobs sj (nolock)
inner join msdb.dbo.sysjobhistory sjh (nolock)
on sj.job_id = sjh.job_id
where sjh.run_status = 4
group by sj.job_id, sj.name, sjh.run_status

Thank-you,
Rubens

Reply to this Message

John Bell - 05 Jul 2008 08:40 GMT

Reply to this Message

Rubens - 08 Jul 2008 00:19 GMT

Thank-you John, this is close to what I need. I was actually able to get this information by e-mailing myself the results of sp_get_composite_job_info.

Perhaps I should explain another major related problem I have for which I have never been able to get an answer to. We have a Web process that our payroll department uses. Before I started as a DBA, this Web process was able to see the status of job executions because it was running under the sa account. A while ago, I created a SQL account for this process to run under because naturally, it's a major security issue to run anything under sa, especially processes controlled by members outside the DBA group.

So since I changed the account, the Web process can no longer tell the status of the job. This causes major problems for the users because kicking off another process is dependent on knowing the first job completed successfully. Ultimately, I need to assign permissions for this new account to be able to see the job status. That's it, plain and simple. I've tried assigning / adjusting various permissions in the msdb TargetServersRole, but cannot seem to get the correct permissions for it to do this.

Can someone tell me what permissions I need to assign for an account to see the current execution status of a job, and importantly, WHEN it is executing? One of the only places I have seen the execution status accurately portrayed is in the result-set of msdb.dbo.sp_get_composite_job_info. I've granted the account EXEC permissions to this proc to no avail.

An answer to this question would avoid so many hassles for me!

Thank-you,
Rubens

"Rubens" <rubensrose@hotmail.com> wrote in message news:ukz9Zxg3IHA.5060@TK2MSFTNGP02.phx.gbl...
I am trying to create a query against a SQL 2000 database to see if a job is currently running. I cannot cancel the job that I am monitoring. Shortly after it completes, I need to notify some people and do some additional work. Unfortunately I forgot to change the job to notify me on completion, so I just want to set up a job to run every 10 minutes that will e-mail me when it completes. However, it doesn't appear SQL logs currently running jobs, just the status once it completes.

Can someone tell me how I can find the status of the job when it is currently running? Here is what I have, which returns no records even though the job is running.

select
sj.job_id
,sj.name
,sjh.run_status
,max(sjh.run_date) as run_date
,max(sjh.run_time) as run_time
from msdb.dbo.sysjobs sj (nolock)
inner join msdb.dbo.sysjobhistory sjh (nolock)
on sj.job_id = sjh.job_id
where sjh.run_status = 4
group by sj.job_id, sj.name, sjh.run_status

Thank-you,
Rubens

Hi Rubens

Try:

select
sj.job_id
,sj.name
,sjh.start_execution_date as run_date
from msdb.dbo.sysjobs sj (nolock)
inner join msdb.dbo.sysjobactivity sjh (nolock)
on sj.job_id = sjh.job_id
where sjh.stop_execution_date IS NULL

John

Reply to this Message

John Bell - 08 Jul 2008 08:00 GMT

Thank-you John, this is close to what I need. I was actually able to get this information by e-mailing myself the results of sp_get_composite_job_info.

Perhaps I should explain another major related problem I have for which I have never been able to get an answer to. We have a Web process that our payroll department uses. Before I started as a DBA, this Web process was able to see the status of job executions because it was running under the sa account. A while ago, I created a SQL account for this process to run under because naturally, it's a major security issue to run anything under sa, especially processes controlled by members outside the DBA group.

So since I changed the account, the Web process can no longer tell the status of the job. This causes major problems for the users because kicking off another process is dependent on knowing the first job completed successfully. Ultimately, I need to assign permissions for this new account to be able to see the job status. That's it, plain and simple. I've tried assigning / adjusting various permissions in the msdb TargetServersRole, but cannot seem to get the correct permissions for it to do this.

Can someone tell me what permissions I need to assign for an account to see the current execution status of a job, and importantly, WHEN it is executing? One of the only places I have seen the execution status accurately portrayed is in the result-set of msdb.dbo.sp_get_composite_job_info. I've granted the account EXEC permissions to this proc to no avail.

An answer to this question would avoid so many hassles for me!

Thank-you,
Rubens
"John Bell" <jbellnewsposts@hotmail.com> wrote in message news:B9F0E75F-244A-4938-B33B-0B2E73966577@microsoft.com...

"Rubens" <rubensrose@hotmail.com> wrote in message news:ukz9Zxg3IHA.5060@TK2MSFTNGP02.phx.gbl...
I am trying to create a query against a SQL 2000 database to see if a job is currently running. I cannot cancel the job that I am monitoring. Shortly after it completes, I need to notify some people and do some additional work. Unfortunately I forgot to change the job to notify me on completion, so I just want to set up a job to run every 10 minutes that will e-mail me when it completes. However, it doesn't appear SQL logs currently running jobs, just the status once it completes.

Can someone tell me how I can find the status of the job when it is currently running? Here is what I have, which returns no records even though the job is running.

select
sj.job_id
,sj.name
,sjh.run_status
,max(sjh.run_date) as run_date
,max(sjh.run_time) as run_time
from msdb.dbo.sysjobs sj (nolock)
inner join msdb.dbo.sysjobhistory sjh (nolock)
on sj.job_id = sjh.job_id
where sjh.run_status = 4
group by sj.job_id, sj.name, sjh.run_status

Thank-you,
Rubens

Hi Rubens

Try:

select
sj.job_id
,sj.name
,sjh.start_execution_date as run_date
from msdb.dbo.sysjobs sj (nolock)
inner join msdb.dbo.sysjobactivity sjh (nolock)
on sj.job_id = sjh.job_id
where sjh.stop_execution_date IS NULL

John

Hi Rubens

If you look at the code for sp_get_composite_job_info it indicates that SQLAgentReaderRole permissions will be able to view all running jobs!

John

Reply to this Message

Rubens - 13 Jul 2008 04:00 GMT

Darn... there I go again not providing enough information! I failed to mention this was for a SQL 2000 server, so that SQLAgentReaderRole does not exist. I need for users to see the status of the job when it is running on a SQL 2000 machine. Is this possible? I have never been able to find an answer to this...

I was able to find a fix however with a real hack, something I don't want to implement in production because it involves changing sp_get_composite_job_info. There is a line of code that checks to see if the user is an administrator, and if so, grants permission. I modified this on a test machine just to see if a user could see running jobs, and it worked. There has to be another solution to this, isn't there?

Thanks again and sorry for the late reply. Been away from my computer for a few days.

Rubens

"Rubens" <rubensrose@hotmail.com> wrote in message news:eVCeufI4IHA.2348@TK2MSFTNGP06.phx.gbl...
Thank-you John, this is close to what I need. I was actually able to get this information by e-mailing myself the results of sp_get_composite_job_info.

Perhaps I should explain another major related problem I have for which I have never been able to get an answer to. We have a Web process that our payroll department uses. Before I started as a DBA, this Web process was able to see the status of job executions because it was running under the sa account. A while ago, I created a SQL account for this process to run under because naturally, it's a major security issue to run anything under sa, especially processes controlled by members outside the DBA group.

So since I changed the account, the Web process can no longer tell the status of the job. This causes major problems for the users because kicking off another process is dependent on knowing the first job completed successfully. Ultimately, I need to assign permissions for this new account to be able to see the job status. That's it, plain and simple. I've tried assigning / adjusting various permissions in the msdb TargetServersRole, but cannot seem to get the correct permissions for it to do this.

Can someone tell me what permissions I need to assign for an account to see the current execution status of a job, and importantly, WHEN it is executing? One of the only places I have seen the execution status accurately portrayed is in the result-set of msdb.dbo.sp_get_composite_job_info. I've granted the account EXEC permissions to this proc to no avail.

An answer to this question would avoid so many hassles for me!

Thank-you,
Rubens
"John Bell" <jbellnewsposts@hotmail.com> wrote in message news:B9F0E75F-244A-4938-B33B-0B2E73966577@microsoft.com...

"Rubens" <rubensrose@hotmail.com> wrote in message news:ukz9Zxg3IHA.5060@TK2MSFTNGP02.phx.gbl...
I am trying to create a query against a SQL 2000 database to see if a job is currently running. I cannot cancel the job that I am monitoring. Shortly after it completes, I need to notify some people and do some additional work. Unfortunately I forgot to change the job to notify me on completion, so I just want to set up a job to run every 10 minutes that will e-mail me when it completes. However, it doesn't appear SQL logs currently running jobs, just the status once it completes.

Can someone tell me how I can find the status of the job when it is currently running? Here is what I have, which returns no records even though the job is running.

select
sj.job_id
,sj.name
,sjh.run_status
,max(sjh.run_date) as run_date
,max(sjh.run_time) as run_time
from msdb.dbo.sysjobs sj (nolock)
inner join msdb.dbo.sysjobhistory sjh (nolock)
on sj.job_id = sjh.job_id
where sjh.run_status = 4
group by sj.job_id, sj.name, sjh.run_status

Thank-you,
Rubens

Hi Rubens

Try:

select
sj.job_id
,sj.name
,sjh.start_execution_date as run_date
from msdb.dbo.sysjobs sj (nolock)
inner join msdb.dbo.sysjobactivity sjh (nolock)
on sj.job_id = sjh.job_id
where sjh.stop_execution_date IS NULL

John

Hi Rubens

If you look at the code for sp_get_composite_job_info it indicates that SQLAgentReaderRole permissions will be able to view all running jobs!

John

Reply to this Message

John Bell - 13 Jul 2008 10:30 GMT

Reply to this Message

Rubens - 13 Jul 2008 15:50 GMT

Hmmm, okay that's interesting John. It did seem to work though. Here is
what I did:

1. Modified sp_get_composite_job info as follows (actually, had to drop and
recreate because you can't modify a system proc):
-- SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @is_sysadmin = 1 --ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)

2. Ensured the msdb TargetServersRole had permission to execute this proc
(we might have had to give other proc's execute access too such as
sp_help_job, sp_help_job_history etc.)
3. Created a test SQL account that only had permission to the TargetServers
role in msdb.
4. Registered the server name using this test account in EM, then checked
the job status' and I could see it was running. And also I could execute
the proc and see what jobs were running there too.

A quick test instead of having to drop / recreate the system proc is to just
make a copy of it with the modification, then execute it as a test user and
you will see the jobs that are running.

Regardless, its good to hear someone say that you had to be sysadmin to see
this in SQL 2000. I've suspected that all along but never has anyone said
that in return nor confirmed that. At least now I have an answer for those
that ask!

:- )

Thanks,
Rubens

> Darn... there I go again not providing enough information! I failed to
> mention this was for a SQL 2000 server, so that SQLAgentReaderRole does
[quoted text clipped - 19 lines]
>
> John

Reply to this Message