Current Job Run Status
in [Server Configuration]
|
Prev: select statement with no results is producing locks
Next: Enabling AWE SQL Server 2005 64-bit - multipleinstances
From: Rubens on 4 Jul 2008 15:29 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
From: John Bell on 5 Jul 2008 03:40 "Rubens" <rubensrose(a)hotmail.com> wrote in message news:ukz9Zxg3IHA.5060(a)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
From: Rubens on 7 Jul 2008 19:19 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(a)hotmail.com> wrote in message news:B9F0E75F-244A-4938-B33B-0B2E73966577(a)microsoft.com... "Rubens" <rubensrose(a)hotmail.com> wrote in message news:ukz9Zxg3IHA.5060(a)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
From: John Bell on 8 Jul 2008 03:00 "Rubens" <rubensrose(a)hotmail.com> wrote in message news:eVCeufI4IHA.2348(a)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(a)hotmail.com> wrote in message news:B9F0E75F-244A-4938-B33B-0B2E73966577(a)microsoft.com... "Rubens" <rubensrose(a)hotmail.com> wrote in message news:ukz9Zxg3IHA.5060(a)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
From: Rubens on 12 Jul 2008 23:00
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 "John Bell" <jbellnewsposts(a)hotmail.com> wrote in message news:4BA8CEE6-1615-4E47-A53C-0547608F9A46(a)microsoft.com... "Rubens" <rubensrose(a)hotmail.com> wrote in message news:eVCeufI4IHA.2348(a)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(a)hotmail.com> wrote in message news:B9F0E75F-244A-4938-B33B-0B2E73966577(a)microsoft.com... "Rubens" <rubensrose(a)hotmail.com> wrote in message news:ukz9Zxg3IHA.5060(a)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 |