Job that runs past the next scheduled start time

Hello group
I have a problem that I'm considering if the SQL Server Agent and Job
scheduling can help me solve.
I need to check some status very often (approximately every 30. sec.). I
have learned that you can get the job sceduling to start this often, so that
should not be a problem.
Normaly the job will just check this status and then stop.
Once in a while the status will signal that further processing should take
place. This processing may last more than 30 seconds, and therefore last
beyond the next start time of the job.
Is this a problem?
Will I have the same job running multiple times? That is not good.
When will the next job then start? Will it start 30 sec. after the long
running job completed, or will jobs queue op while the long running job is
processing so that I will get multiple starts when the first job completes?
I hope that you can understand my issue.
Regards
Anders> Will I have the same job running multiple times?
No. Agent is simple in this regard. If the job is already running when it is supposed to start, then
Agent will *not* start another instance of that job.
> When will the next job then start? Will it start 30 sec. after the long running job completed, or
> will jobs queue op while the long running job is processing so that I will get multiple starts
> when the first job completes?
Neither. It will start when it is supposed to. Say for simplicity of this example that you want to
start it every minute. Now, you have one occasion where it starts at 13:00:00 and it runs for 110
seconds. The job will not start at 13:01:00 since it is already running. As I said, this occasion
takes 110 seconds so it finishes at 13:01:50. Next time it is supposed to start is 13:02:00, and now
it isn't running so Agent will start it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Anders K. Olsen" <akol_dk@.hotmail.com> wrote in message
news:OM4PQuc%23HHA.700@.TK2MSFTNGP05.phx.gbl...
> Hello group
> I have a problem that I'm considering if the SQL Server Agent and Job scheduling can help me
> solve.
> I need to check some status very often (approximately every 30. sec.). I have learned that you can
> get the job sceduling to start this often, so that should not be a problem.
> Normaly the job will just check this status and then stop.
> Once in a while the status will signal that further processing should take place. This processing
> may last more than 30 seconds, and therefore last beyond the next start time of the job.
> Is this a problem?
> Will I have the same job running multiple times? That is not good.
> When will the next job then start? Will it start 30 sec. after the long running job completed, or
> will jobs queue op while the long running job is processing so that I will get multiple starts
> when the first job completes?
> I hope that you can understand my issue.
> Regards
> Anders
>|||Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23GdwCgd%23HHA.1208@.TK2MSFTNGP03.phx.gbl...
>> Will I have the same job running multiple times?
> No. Agent is simple in this regard. If the job is already running when it
> is supposed to start, then Agent will *not* start another instance of that
> job.
Sometimes simple is good :-)
>> When will the next job then start? Will it start 30 sec. after the long
>> running job completed, or will jobs queue op while the long running job
>> is processing so that I will get multiple starts when the first job
>> completes?
> Neither. It will start when it is supposed to. Say for simplicity of this
> example that you want to start it every minute. Now, you have one occasion
> where it starts at 13:00:00 and it runs for 110 seconds. The job will not
> start at 13:01:00 since it is already running. As I said, this occasion
> takes 110 seconds so it finishes at 13:01:50. Next time it is supposed to
> start is 13:02:00, and now it isn't running so Agent will start it.
This is fine for our purposes, so we will continue along this line.
Thank you for your help.
Regards
Anderssql

Job Status Wrong

Hello,
In my Enterprise Manager i see one job that i know its running(i look
locally on server) and jobs status appear like job is not executing(job
status is wrong)
Is there any patch to apply to EM?
Thanks and best regards.
Hi
I take it that by wrong status, you mean it is showing as sleeping under
current activity in Enterprise Manager. This is quiet normal as Sql Server
pages in and out processes as it runs them. If you keep refreshing it you may
catch it as active.
If you want to check that it is actually doing something, you can monitor it
with profiler or if you double click on the current activity, you get details
of the current SQL statement being performed. If you refresh that you should
see it change.
Hope this helps
John
"CC&JM" wrote:

> Hello,
> In my Enterprise Manager i see one job that i know its running(i look
> locally on server) and jobs status appear like job is not executing(job
> status is wrong)
> Is there any patch to apply to EM?
> Thanks and best regards.
|||Hello John,
Thanks for your answer, now i see the status of the job quit well but
yesterday i had a job that runs 4 example at 10:00 12:00 14:00 and so on..
and at 18:00 when i look at EM "Last Run Status" showed 10:00 and "Next Run
Date" showed 12:00.
I know that the problem was with my EM because in the EM of my partner i saw
the job status normally.
Thanks John,
Best regards
"John Bandettini" wrote:
[vbcol=seagreen]
> Hi
> I take it that by wrong status, you mean it is showing as sleeping under
> current activity in Enterprise Manager. This is quiet normal as Sql Server
> pages in and out processes as it runs them. If you keep refreshing it you may
> catch it as active.
> If you want to check that it is actually doing something, you can monitor it
> with profiler or if you double click on the current activity, you get details
> of the current SQL statement being performed. If you refresh that you should
> see it change.
> Hope this helps
> John
> "CC&JM" wrote:
|||Hi
I misunderstood your problem, I thought you were looking at current
activity. If you were looking at jobs and the job did not seem to be running,
did you refresh the display? Once you have opened something in Enterprise
manager, like jobs it will not automatically refresh. You need to either
right click and choose refresh or press F5.
Regards
John
"CC&JM" wrote:
[vbcol=seagreen]
> Hello John,
> Thanks for your answer, now i see the status of the job quit well but
> yesterday i had a job that runs 4 example at 10:00 12:00 14:00 and so on..
> and at 18:00 when i look at EM "Last Run Status" showed 10:00 and "Next Run
> Date" showed 12:00.
> I know that the problem was with my EM because in the EM of my partner i saw
> the job status normally.
> Thanks John,
> Best regards
> "John Bandettini" wrote:
sql

Job Status Wrong

Hello,
In my Enterprise Manager i see one job that i know its running(i look
locally on server) and jobs status appear like job is not executing(job
status is wrong)
Is there any patch to apply to EM?
Thanks and best regards.Hi
I take it that by wrong status, you mean it is showing as sleeping under
current activity in Enterprise Manager. This is quiet normal as Sql Server
pages in and out processes as it runs them. If you keep refreshing it you may
catch it as active.
If you want to check that it is actually doing something, you can monitor it
with profiler or if you double click on the current activity, you get details
of the current SQL statement being performed. If you refresh that you should
see it change.
Hope this helps
John
"CC&JM" wrote:
> Hello,
> In my Enterprise Manager i see one job that i know its running(i look
> locally on server) and jobs status appear like job is not executing(job
> status is wrong)
> Is there any patch to apply to EM?
> Thanks and best regards.|||Hello John,
Thanks for your answer, now i see the status of the job quit well but
yesterday i had a job that runs 4 example at 10:00 12:00 14:00 and so on..
and at 18:00 when i look at EM "Last Run Status" showed 10:00 and "Next Run
Date" showed 12:00.
I know that the problem was with my EM because in the EM of my partner i saw
the job status normally.
Thanks John,
Best regards
"John Bandettini" wrote:
> Hi
> I take it that by wrong status, you mean it is showing as sleeping under
> current activity in Enterprise Manager. This is quiet normal as Sql Server
> pages in and out processes as it runs them. If you keep refreshing it you may
> catch it as active.
> If you want to check that it is actually doing something, you can monitor it
> with profiler or if you double click on the current activity, you get details
> of the current SQL statement being performed. If you refresh that you should
> see it change.
> Hope this helps
> John
> "CC&JM" wrote:
> > Hello,
> >
> > In my Enterprise Manager i see one job that i know its running(i look
> > locally on server) and jobs status appear like job is not executing(job
> > status is wrong)
> > Is there any patch to apply to EM?
> >
> > Thanks and best regards.|||Hi
I misunderstood your problem, I thought you were looking at current
activity. If you were looking at jobs and the job did not seem to be running,
did you refresh the display? Once you have opened something in Enterprise
manager, like jobs it will not automatically refresh. You need to either
right click and choose refresh or press F5.
Regards
John
"CC&JM" wrote:
> Hello John,
> Thanks for your answer, now i see the status of the job quit well but
> yesterday i had a job that runs 4 example at 10:00 12:00 14:00 and so on..
> and at 18:00 when i look at EM "Last Run Status" showed 10:00 and "Next Run
> Date" showed 12:00.
> I know that the problem was with my EM because in the EM of my partner i saw
> the job status normally.
> Thanks John,
> Best regards
> "John Bandettini" wrote:
> > Hi
> >
> > I take it that by wrong status, you mean it is showing as sleeping under
> > current activity in Enterprise Manager. This is quiet normal as Sql Server
> > pages in and out processes as it runs them. If you keep refreshing it you may
> > catch it as active.
> >
> > If you want to check that it is actually doing something, you can monitor it
> > with profiler or if you double click on the current activity, you get details
> > of the current SQL statement being performed. If you refresh that you should
> > see it change.
> >
> > Hope this helps
> >
> > John
> >
> > "CC&JM" wrote:
> >
> > > Hello,
> > >
> > > In my Enterprise Manager i see one job that i know its running(i look
> > > locally on server) and jobs status appear like job is not executing(job
> > > status is wrong)
> > > Is there any patch to apply to EM?
> > >
> > > Thanks and best regards.

Job Status Wrong

Hello,
In my Enterprise Manager i see one job that i know its running(i look
locally on server) and jobs status appear like job is not executing(job
status is wrong)
Is there any patch to apply to EM?
Thanks and best regards.Hi
I take it that by wrong status, you mean it is showing as sleeping under
current activity in Enterprise Manager. This is quiet normal as Sql Server
pages in and out processes as it runs them. If you keep refreshing it you ma
y
catch it as active.
If you want to check that it is actually doing something, you can monitor it
with profiler or if you double click on the current activity, you get detail
s
of the current SQL statement being performed. If you refresh that you should
see it change.
Hope this helps
John
"CC&JM" wrote:

> Hello,
> In my Enterprise Manager i see one job that i know its running(i look
> locally on server) and jobs status appear like job is not executing(job
> status is wrong)
> Is there any patch to apply to EM?
> Thanks and best regards.|||Hello John,
Thanks for your answer, now i see the status of the job quit well but
yesterday i had a job that runs 4 example at 10:00 12:00 14:00 and so on..
and at 18:00 when i look at EM "Last Run Status" showed 10:00 and "Next Run
Date" showed 12:00.
I know that the problem was with my EM because in the EM of my partner i saw
the job status normally.
Thanks John,
Best regards
"John Bandettini" wrote:
[vbcol=seagreen]
> Hi
> I take it that by wrong status, you mean it is showing as sleeping under
> current activity in Enterprise Manager. This is quiet normal as Sql Server
> pages in and out processes as it runs them. If you keep refreshing it you
may
> catch it as active.
> If you want to check that it is actually doing something, you can monitor
it
> with profiler or if you double click on the current activity, you get deta
ils
> of the current SQL statement being performed. If you refresh that you shou
ld
> see it change.
> Hope this helps
> John
> "CC&JM" wrote:
>|||Hi
I misunderstood your problem, I thought you were looking at current
activity. If you were looking at jobs and the job did not seem to be running
,
did you refresh the display? Once you have opened something in Enterprise
manager, like jobs it will not automatically refresh. You need to either
right click and choose refresh or press F5.
Regards
John
"CC&JM" wrote:
[vbcol=seagreen]
> Hello John,
> Thanks for your answer, now i see the status of the job quit well but
> yesterday i had a job that runs 4 example at 10:00 12:00 14:00 and so on..
> and at 18:00 when i look at EM "Last Run Status" showed 10:00 and "Next Ru
n
> Date" showed 12:00.
> I know that the problem was with my EM because in the EM of my partner i s
aw
> the job status normally.
> Thanks John,
> Best regards
> "John Bandettini" wrote:
>

Job Status Suspended

i am trying to code a proc that tests whether a job is running. i
understand how to get job status from the xp_sqlagent_enum_jobs via the
current execution status and i also understand that status = 4 means
the job is idle. what does status =5 (suspended) mean? The BOL
information seems to indicate that the job isnt running when status is
either 4 or 5 (status 0 = not idle and not suspended)
thanks
markHow about this bit of TSQL
select step_id FROM
msdb.dbo.sysjobhistory
WHERE instance_id =
(SELECT max(instance_id) FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id=j.job_id
WHERE j.[name] = 'Your Job Name')
This will extract the current step that a job is on. If a has completed, the
step_id will be zero. If it hasn't completed, the step_id will be non-zero.
"markfcook@.gmail.com" wrote:

> i am trying to code a proc that tests whether a job is running. i
> understand how to get job status from the xp_sqlagent_enum_jobs via the
> current execution status and i also understand that status = 4 means
> the job is idle. what does status =5 (suspended) mean? The BOL
> information seems to indicate that the job isnt running when status is
> either 4 or 5 (status 0 = not idle and not suspended)
> thanks
> mark
>

Job status is not updating

Hello all,
I have a strange and annoying problem with SQL Server 2000 SP3
(I can't upgrade to SP4 at this moment.)
I have a maintenance plan consisting of three scheduled jobs.
Starting couple of weeks ago the Agent fails to update the
last execution time and job outcome for these jobs.
I thought the problem was with the maintenance palns - as I added
extra steps - but recreating the plans did not help.
The jobs are executed - I can see the job outcomes in a form of
created backup files. The Maintenance Plan History also confirms
the successful execution.
But the last run date/time is 0 and the job outcome is 5 - unknown.
Both the Server and the Agent services are running under Local System
account. Agent connects with Windows integrated security.
Any ideas why and what needs to be done to fix?
TIA,
Y.S.Hello,
We found that similar issue is caused by that the TCP/IP Port had a
different values in the Client and the Server Network utilities.
I suggest that you make same port settings for server/client netowrk
utility and restart the SQL Server /Agent services to test the situation.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Peter Yang [MSFT] wrote:
> Hello,
> We found that similar issue is caused by that the TCP/IP Port had a
> different values in the Client and the Server Network utilities.
> I suggest that you make same port settings for server/client netowrk
> utility and restart the SQL Server /Agent services to test the situation.
In fact, the ports were exactly the same, that is 1433, but
thank you anyway - I will be aware of such problem.
However, I was able to fix the problem by connecting the Agent with
'sa' account (it was using integrated security running under Local System.)
Strange, as I never before (and we have 40+ SQL servers in the
datacenter, many of which use integrated security) experienced
anything like this.
Again, the problem was only in status update - the job itself was
executed successfully.
YuraS

> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hello,
You mean you connect to SQL server from EM as sa and and the issue does not
exist? Or you mean you start SQL agent service as a domain account with
system admin role?
Based on my further research, I found the following known issue about job
status
831366 PRB: Job Status is Not Refreshed When You Connect Using Incorrect
Case for the Login Name
http://support.microsoft.com/defaul...kb;EN-US;831366
Please check if this apply to your situation
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.sql

Job status is not updating

Hello all,
I have a strange and annoying problem with SQL Server 2000 SP3
(I can't upgrade to SP4 at this moment.)
I have a maintenance plan consisting of three scheduled jobs.
Starting couple of weeks ago the Agent fails to update the
last execution time and job outcome for these jobs.
I thought the problem was with the maintenance palns - as I added
extra steps - but recreating the plans did not help.
The jobs are executed - I can see the job outcomes in a form of
created backup files. The Maintenance Plan History also confirms
the successful execution.
But the last run date/time is 0 and the job outcome is 5 - unknown.
Both the Server and the Agent services are running under Local System
account. Agent connects with Windows integrated security.
Any ideas why and what needs to be done to fix?
TIA,
Y.S.Hello,
We found that similar issue is caused by that the TCP/IP Port had a
different values in the Client and the Server Network utilities.
I suggest that you make same port settings for server/client netowrk
utility and restart the SQL Server /Agent services to test the situation.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Peter Yang [MSFT] wrote:
> Hello,
> We found that similar issue is caused by that the TCP/IP Port had a
> different values in the Client and the Server Network utilities.
> I suggest that you make same port settings for server/client netowrk
> utility and restart the SQL Server /Agent services to test the situation.
In fact, the ports were exactly the same, that is 1433, but
thank you anyway - I will be aware of such problem.
However, I was able to fix the problem by connecting the Agent with
'sa' account (it was using integrated security running under Local System.)
Strange, as I never before (and we have 40+ SQL servers in the
datacenter, many of which use integrated security) experienced
anything like this.
Again, the problem was only in status update - the job itself was
executed successfully.
YuraS
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello,
You mean you connect to SQL server from EM as sa and and the issue does not
exist? Or you mean you start SQL agent service as a domain account with
system admin role?
Based on my further research, I found the following known issue about job
status
831366 PRB: Job Status is Not Refreshed When You Connect Using Incorrect
Case for the Login Name
http://support.microsoft.com/default.aspx?scid=kb;EN-US;831366
Please check if this apply to your situation
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Job status is not updating

Hello all,
I have a strange and annoying problem with SQL Server 2000 SP3
(I can't upgrade to SP4 at this moment.)
I have a maintenance plan consisting of three scheduled jobs.
Starting couple of weeks ago the Agent fails to update the
last execution time and job outcome for these jobs.
I thought the problem was with the maintenance palns - as I added
extra steps - but recreating the plans did not help.
The jobs are executed - I can see the job outcomes in a form of
created backup files. The Maintenance Plan History also confirms
the successful execution.
But the last run date/time is 0 and the job outcome is 5 - unknown.
Both the Server and the Agent services are running under Local System
account. Agent connects with Windows integrated security.
Any ideas why and what needs to be done to fix?
TIA,
Y.S.
Hello,
We found that similar issue is caused by that the TCP/IP Port had a
different values in the Client and the Server Network utilities.
I suggest that you make same port settings for server/client netowrk
utility and restart the SQL Server /Agent services to test the situation.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Peter Yang [MSFT] wrote:
> Hello,
> We found that similar issue is caused by that the TCP/IP Port had a
> different values in the Client and the Server Network utilities.
> I suggest that you make same port settings for server/client netowrk
> utility and restart the SQL Server /Agent services to test the situation.
In fact, the ports were exactly the same, that is 1433, but
thank you anyway - I will be aware of such problem.
However, I was able to fix the problem by connecting the Agent with
'sa' account (it was using integrated security running under Local System.)
Strange, as I never before (and we have 40+ SQL servers in the
datacenter, many of which use integrated security) experienced
anything like this.
Again, the problem was only in status update - the job itself was
executed successfully.
YuraS

> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hello,
You mean you connect to SQL server from EM as sa and and the issue does not
exist? Or you mean you start SQL agent service as a domain account with
system admin role?
Based on my further research, I found the following known issue about job
status
831366PRB: Job Status is Not Refreshed When You Connect Using Incorrect
Case for the Login Name
http://support.microsoft.com/default...b;EN-US;831366
Please check if this apply to your situation
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Job Status

Hey guys,

I need to know the current status for a job that i started.

I knew that sp_help_job give me all this information, but i have to do IFs, so I have to set a variable with this result. I only need the columns last_run_outcome and current_execution_status.

How do I to get only this values from a Job_id ??

i Looked in almost all table from msdb to find these 2 columns and nothing ... :/

Anyone can helpme ?The Answer Is :

use master
exec sp_addlinkedserver @.server = 'LocalServer',
@.srvproduct = '',
@.provider = 'SQLOLEDB',
@.datasrc = @.@.servername
go

select last_run_outcome, current_execution_status from openquery(LocalServer, 'set fmtonly off; exec msdb.dbo.sp_help_job')
go
exec sp_dropserver 'LocalServer'

U donot need to drop the linked server, I did that to ensure that noerror will be shoed when adding linked servers

Bye

job status

Hi,
Is it possible to find out the current status of a job (sql 2000 sql job),
i.e. whether it is idle/running/failed, etc
Thanks in advance!!Try giving select privilages to msdb..sysjobhistory and msdb..sysjobs
tables.
select name,run_status from msdb..sysjobhistory a,sysjobs b
where a.job_id=b.job_id
and name='name of the job'
explanation for run_status:- You could write a case statement with
run_status with below description.
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Thanks
Hari
"AppleDopod" <hk_007@.hotmail.com> wrote in message
news:%23NlymnYUHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Is it possible to find out the current status of a job (sql 2000 sql job),
> i.e. whether it is idle/running/failed, etc
> Thanks in advance!!
>|||Please have a look at this script:
http://www.replicationanswers.com/Downloads/KillRunningJobs.txt. You can
use -- 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle,
5 = Suspended, 6 = [obsolete], 7 = PerformingCompletionActions to modify it
as you like.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.comsql

job status

Hi,
Is it possible to find out the current status of a job (sql 2000 sql job),
i.e. whether it is idle/running/failed, etc
Thanks in advance!!Try giving select privilages to msdb..sysjobhistory and msdb..sysjobs
tables.
select name,run_status from msdb..sysjobhistory a,sysjobs b
where a.job_id=b.job_id
and name='name of the job'
explanation for run_status:- You could write a case statement with
run_status with below description.
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Thanks
Hari
"AppleDopod" <hk_007@.hotmail.com> wrote in message
news:%23NlymnYUHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Is it possible to find out the current status of a job (sql 2000 sql job),
> i.e. whether it is idle/running/failed, etc
> Thanks in advance!!
>|||Please have a look at this script:
http://www.replicationanswers.com/D...unningJobs.txt. You can
use -- 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle,
5 = Suspended, 6 = [obsolete], 7 = PerformingCompletionActions to modify
it
as you like.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

job status

Hi,
Is it possible to find out the current status of a job (sql 2000 sql job),
i.e. whether it is idle/running/failed, etc
Thanks in advance!!
Try giving select privilages to msdb..sysjobhistory and msdb..sysjobs
tables.
select name,run_status from msdb..sysjobhistory a,sysjobs b
where a.job_id=b.job_id
and name='name of the job'
explanation for run_status:- You could write a case statement with
run_status with below description.
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Thanks
Hari
"AppleDopod" <hk_007@.hotmail.com> wrote in message
news:%23NlymnYUHHA.4872@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Is it possible to find out the current status of a job (sql 2000 sql job),
> i.e. whether it is idle/running/failed, etc
> Thanks in advance!!
>
|||Please have a look at this script:
http://www.replicationanswers.com/Downloads/KillRunningJobs.txt. You can
use -- 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 = Idle,
5 = Suspended, 6 = [obsolete], 7 = PerformingCompletionActions to modify it
as you like.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Job scheduler

Hi all
In which table in msdb Database the Executing status of
the job is stored. I need the table name - which holds the
information like
1. Job started.
2. Job executing
3. Job completed info.
The above status may be in the same field of the table
Thanks in advance
Anand.Hi,
Data will be stored in SYSJOBHISTORY table of MSDB database
Column name is : RUN_STATUS
The column hols the below status,
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Since querying the system table is not advisible , you can execute the
system procedure "SP_HELP_JOBHISTORY" to get the information regarding the
status of each scheduled task.
Thanks
Hari
MCDBA
"Guru S. Anand" <anonymous@.discussions.microsoft.com> wrote in message
news:2d1b01c3fcd6$10b00d80$a001280a@.phx.gbl...
> Hi all
> In which table in msdb Database the Executing status of
> the job is stored. I need the table name - which holds the
> information like
> 1. Job started.
> 2. Job executing
> 3. Job completed info.
> The above status may be in the same field of the table
> Thanks in advance
> Anand.|||Thanks Hari
Anand.

>--Original Message--
>Hi,
>Data will be stored in SYSJOBHISTORY table of MSDB
database
>Column name is : RUN_STATUS
>The column hols the below status,
>0 = Failed
>1 = Succeeded
>2 = Retry
>3 = Canceled
>4 = In progress
>Since querying the system table is not advisible , you
can execute the
>system procedure "SP_HELP_JOBHISTORY" to get the
information regarding the
>status of each scheduled task.
>Thanks
>Hari
>MCDBA
>
>"Guru S. Anand" <anonymous@.discussions.microsoft.com>
wrote in message
>news:2d1b01c3fcd6$10b00d80$a001280a@.phx.gbl...
the
>
>.
>

Job scheduler

Hi all
In which table in msdb Database the Executing status of
the job is stored. I need the table name - which holds the
information like
1. Job started.
2. Job executing
3. Job completed info.
The above status may be in the same field of the table
Thanks in advance
Anand.Hi,
Data will be stored in SYSJOBHISTORY table of MSDB database
Column name is : RUN_STATUS
The column hols the below status,
0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
4 = In progress
Since querying the system table is not advisible , you can execute the
system procedure "SP_HELP_JOBHISTORY" to get the information regarding the
status of each scheduled task.
Thanks
Hari
MCDBA
"Guru S. Anand" <anonymous@.discussions.microsoft.com> wrote in message
news:2d1b01c3fcd6$10b00d80$a001280a@.phx.gbl...
> Hi all
> In which table in msdb Database the Executing status of
> the job is stored. I need the table name - which holds the
> information like
> 1. Job started.
> 2. Job executing
> 3. Job completed info.
> The above status may be in the same field of the table
> Thanks in advance
> Anand.|||Thanks Hari
Anand.
>--Original Message--
>Hi,
>Data will be stored in SYSJOBHISTORY table of MSDB
database
>Column name is : RUN_STATUS
>The column hols the below status,
>0 = Failed
>1 = Succeeded
>2 = Retry
>3 = Canceled
>4 = In progress
>Since querying the system table is not advisible , you
can execute the
>system procedure "SP_HELP_JOBHISTORY" to get the
information regarding the
>status of each scheduled task.
>Thanks
>Hari
>MCDBA
>
>"Guru S. Anand" <anonymous@.discussions.microsoft.com>
wrote in message
>news:2d1b01c3fcd6$10b00d80$a001280a@.phx.gbl...
>> Hi all
>> In which table in msdb Database the Executing status of
>> the job is stored. I need the table name - which holds
the
>> information like
>> 1. Job started.
>> 2. Job executing
>> 3. Job completed info.
>> The above status may be in the same field of the table
>> Thanks in advance
>> Anand.
>
>.
>

job running with status performing completion action

I noticed this morning that
I have a job status "performing completion action" for one of mine sql scheduled task job.

The job executes a dts pkg (which I just rum manually, and all worked fine)

What does it mean?, I can not stop this job and refresh is not working ether.
What I can do?

Thank you,
Lclose your em and reopen it. it does not refresh very well.|||Sorry,
What is a EM and how I can close it / reopen it.
I'm a user, who is just trying to find out more about processies.

Thank you|||enterprise manager has a problem refreshing sometimes. if you are looking at jobs, i assume this is the application you are looking at.|||I did close it, the message still there.|||close your em and reopen it. it does not refresh very well...or right-click on the job and choose "refresh job" ;)

I also had this happen recently because our jobs are set up to send email notifications when the job is complete, and the servers(s) in question were unable to send emails because the network was having email problems. I ended up stopping and starting the mail service (xp_stopmail, xp_startmail) and the problem was fixed.

Then I had to send an abusive email off to the network admin group for not letting us know when they were going to intentionally damage the network.|||THANK YOU FOR ALL SUGGESTIONS, BUT WE HAD TO RESTART THE SERVER.
AS WELL AS OUT MAIL SERVER WAS REBOOTED.

jOB TASK WAS NOT ABLE TO SEND AN E-MAIL DUE TO MAIL SERVER FAILRE,

THANK YOU|||Yeah, I was gonna say...

issue the xp_stopmail and xp_startmail on the affected server, OR reboot the entire infrastructure.

either one will work ;)

Job running status

In SQL 2000, go to Management, than Jobs, you will see a list of all jobs and their running status. I would like to programmatically display the same on my web page. I can call sysjobs table to get the jobs and use sysjobschedules to determine whether it's been scheduled or not. Then use sysjobhistory to obtain their running history. But how and where do I get the Next Run Date information? Is this calculated or stored somewhere?

Can anyone give me some help on how to work with sysjobs, sysjobschedules, and sysjobhistory tables? All I am trying to do is to build a web-based app that does the same thing as the Enterprise Manager is doing - create and schedule a job, and monitor the job status.

Thanks a lot!

Penn

Performing query directly to system tables is not recommended, as the schema of system tables/views may change even between different builds. Instead, you can use some system procedures to get detailed information about agent jobs, for example:

exec msdb..sp_help_job @.job_name='Expired subscription clean up'

So you can call such system procedures from your code using SqlDataAdapter and then fill the result sets into DataSet for future using. For more information about system procedures, please refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_00_519s.asp

Job notification status on sql server 8

I wanted to send notification to several users regarding the job status on sql server whether it was successful or failed. I am using windows 2003 server and our domain is operating on webmail which is not a microsft product. I am a newbie so if anyone could help me out on what procedures should I take to make this work.

Any help would be very much appreciated.

As long as your mail server is using smtp, you should only have to specify the proper smtp service and port name in the setup. What version of SQL2005 are you using? I'm using Standard Edition and SqlExpress Advanced Tools on subscriber's stations. We have a mail smtp server, and that's all I had to do.

Do you have database mail enabled and configured?

Check this link: http://msdn2.microsoft.com/en-us/library/ms191442.aspx

and this one, which has a link to a demo tutorial on setting database mail up.

http://www.microsoft.com/technet/community/events/sql2005/sql-01.mspx

Good luck, and let us know how it turns out.

Job LastRunOutCome?

Hi all,
I am trying to determine the status of a Job after it has finished
running, but I don't get the correct results:
I first check if the Job is currently Running:
Private Function isJobExecuting(sJobName As String) As Boolean
Dim JobServer As String
'Execute a SQL Server Job here
'Initialise the SQL Server
Set oSQLServer = New SQLDMO.SQLServer
'Initialise the Login time out
oSQLServer.LoginTimeout = 10
'Disconnect any connections first
oSQLServer.Disconnect
'Connect to the Server
oSQLServer.Connect JobServer, CStr("sa"), CStr("")
'Set Job and start it
oSQLServer.JobServer.Jobs.Refresh
Set oJob = oSQLServer.JobServer.Jobs(sJobName)
'Before starting to run the Job the status must be checkes...
'And run the job only when its not currently running.
If (oSQLServer.JobServer.Jobs(sJobName).CurrentRunStatus =
SQLDMOJobExecution_Executing) Then
isJobExecuting = True
ElseIf (oSQLServer.JobServer.Jobs(sJobName).CurrentRunStatus =
SQLDMOJobExecution_PerformingCompletionA
ctions) Then
isJobExecuting = False
End If
End Function
If Not I then Start the Job and check the status after that..
Everytime it tells me the status is 5...which means it cannot determine
the status.
While (isJobExecuting(JobName))
MsgBox ("RiskWatch Download Job busy executing. Please
wait...")
Wend
oJob.start
'After the Job has finished Check the Status...
While (isJobExecuting(JobName))
Wend
MsgBox oSQLServer.JobServer.Jobs(JobName).CurrentRunStatus
' Close the Job connection when done
Set oJob = Nothing
oSQLServer.JobServer.Jobs.Refresh
JobStatus = oSQLServer.JobServer.Jobs(JobName).LastRunOutcome
If JobStatus = 0 Then
MsgBox ("RiskWatch Download Job Failed ...")
RiskWatchDownload = False
Unload Me
ElseIf JobStatus = 1 Then
RiskWatchDownload = True
End If
I really will appreciate ur help!!
Regards,
Phonzo.Hi, Phonzo
Your isJobExecuting function returns False when CurrentRunStatus =
SQLDMOJobExecution_PerformingCompletionA
ctions, but also for any other
status other than SQLDMOJobExecution_Executing (because if you don't
assign any value to a boolean variable, it remains False). According to
Books Online, SQLDMOJobExecution_PerformingCompletionA
ctions means "All
executable job steps complete. Job history logging in progress". Maybe
you should wait until CurrentRunStatus = SQLDMOJobExecution_Idle before
looking at LastRunOutcome.
Razvan|||Hey Raz,
Thanks a lot 4 ur help, it worked...
Tnx a mil,
Cheers,
Phonzo.