How to set SQL Job continuously run

Hello,
I want to set up one SQL job to continuously run with
minimum break. In another word, the second run of the SQL
Job will start as soon as the first run is completed. Is
there any way to do it?
Thanks a lot,
LixinChange the job step properties, so that when the step completes, it runs it
self again, instead of completing successfully.
--
Denny Cherry
DBA
GameSpy Industries
"Lixin Fan" <lixinf@.mailcode.com> wrote in message
news:299801c386ce$22c337f0$a001280a@.phx.gbl...
> Hello,
> I want to set up one SQL job to continuously run with
> minimum break. In another word, the second run of the SQL
> Job will start as soon as the first run is completed. Is
> there any way to do it?
> Thanks a lot,
> Lixin|||Denny,
Thank you very much.
Since the Job becomes an endless process, do you think
this will cause some potential issues?
Lixin
>--Original Message--
>Change the job step properties, so that when the step
completes, it runs it
>self again, instead of completing successfully.
>--
>Denny Cherry
>DBA
>GameSpy Industries
>"Lixin Fan" <lixinf@.mailcode.com> wrote in message
>news:299801c386ce$22c337f0$a001280a@.phx.gbl...
>> Hello,
>> I want to set up one SQL job to continuously run with
>> minimum break. In another word, the second run of the
SQL
>> Job will start as soon as the first run is completed. Is
>> there any way to do it?
>> Thanks a lot,
>> Lixin
>
>.
>|||It "shouldn't". I've run a job in an endless loop before. I would be an
escape clause in there every day, or every hour so that it exits, and writes
the job history to disk. You can use sp_stop_job to stop the job in it's
tracks. Something like
if datepart(hh, getdate()) = 0 and datepart(mi, getdate()) = 0
exec sp_Stop_Job 'JobName'
If you wanted to escape every hour then remove the "datepart(hh, getdate())
= 0 and " from the if statement. Besure to schedule the job to start every
minute of every day, so that when it stops it will automatically restart.
--
Denny Cherry
DBA
GameSpy Industries
"Lixin Fan" <lixinf@.mailcode.com> wrote in message
news:18e9901c386db$09351760$a601280a@.phx.gbl...
> Denny,
> Thank you very much.
> Since the Job becomes an endless process, do you think
> this will cause some potential issues?
> Lixin
> >--Original Message--
> >Change the job step properties, so that when the step
> completes, it runs it
> >self again, instead of completing successfully.
> >
> >--
> >Denny Cherry
> >DBA
> >GameSpy Industries
> >"Lixin Fan" <lixinf@.mailcode.com> wrote in message
> >news:299801c386ce$22c337f0$a001280a@.phx.gbl...
> >> Hello,
> >>
> >> I want to set up one SQL job to continuously run with
> >> minimum break. In another word, the second run of the
> SQL
> >> Job will start as soon as the first run is completed. Is
> >> there any way to do it?
> >>
> >> Thanks a lot,
> >> Lixin
> >
> >
> >.
> >|||> if datepart(hh, getdate()) = 0 and datepart(mi, getdate()) = 0
> exec sp_Stop_Job 'JobName'
This may not guarantee that sp_stop_job will always run because the
currently running job may not stop before 00:00. It's safer to do a time
range check.
Another mehtod to run a job repeatly, especially if you don't need the loop
to be very tight, is to schedule the job to start every minute (or any other
time unit). In the job, using an apllication lock to detect whether the job
is still running. Exit immediately if it is.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Denny" <mrdenny@.gamespy.com> wrote in message
news:eKtYe4uhDHA.2448@.TK2MSFTNGP12.phx.gbl...
> It "shouldn't". I've run a job in an endless loop before. I would be an
> escape clause in there every day, or every hour so that it exits, and
writes
> the job history to disk. You can use sp_stop_job to stop the job in it's
> tracks. Something like
> if datepart(hh, getdate()) = 0 and datepart(mi, getdate()) = 0
> exec sp_Stop_Job 'JobName'
> If you wanted to escape every hour then remove the "datepart(hh,
getdate())
> = 0 and " from the if statement. Besure to schedule the job to start
every
> minute of every day, so that when it stops it will automatically restart.
> --
> Denny Cherry
> DBA
> GameSpy Industries
> "Lixin Fan" <lixinf@.mailcode.com> wrote in message
> news:18e9901c386db$09351760$a601280a@.phx.gbl...
> > Denny,
> >
> > Thank you very much.
> >
> > Since the Job becomes an endless process, do you think
> > this will cause some potential issues?
> >
> > Lixin
> >
> > >--Original Message--
> > >Change the job step properties, so that when the step
> > completes, it runs it
> > >self again, instead of completing successfully.
> > >
> > >--
> > >Denny Cherry
> > >DBA
> > >GameSpy Industries
> > >"Lixin Fan" <lixinf@.mailcode.com> wrote in message
> > >news:299801c386ce$22c337f0$a001280a@.phx.gbl...
> > >> Hello,
> > >>
> > >> I want to set up one SQL job to continuously run with
> > >> minimum break. In another word, the second run of the
> > SQL
> > >> Job will start as soon as the first run is completed. Is
> > >> there any way to do it?
> > >>
> > >> Thanks a lot,
> > >> Lixin
> > >
> > >
> > >.
> > >
>|||> Another mehtod to run a job repeatly, especially if you don't need the loop
> to be very tight, is to schedule the job to start every minute (or any other
> time unit). In the job, using an apllication lock to detect whether the job
> is still running. Exit immediately if it is.
Is that necessary? As far as I understand, Agent will not start a job if it is already running.
Yet another option is to start the job at agent startup and use a loop with in the job (assuming one
TSQL jobstep). The loop is using a simple WAITFOR construct.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:u1ENtQwhDHA.3276@.tk2msftngp13.phx.gbl...
> > if datepart(hh, getdate()) = 0 and datepart(mi, getdate()) = 0
> > exec sp_Stop_Job 'JobName'
> This may not guarantee that sp_stop_job will always run because the
> currently running job may not stop before 00:00. It's safer to do a time
> range check.
> Another mehtod to run a job repeatly, especially if you don't need the loop
> to be very tight, is to schedule the job to start every minute (or any other
> time unit). In the job, using an apllication lock to detect whether the job
> is still running. Exit immediately if it is.
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Denny" <mrdenny@.gamespy.com> wrote in message
> news:eKtYe4uhDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > It "shouldn't". I've run a job in an endless loop before. I would be an
> > escape clause in there every day, or every hour so that it exits, and
> writes
> > the job history to disk. You can use sp_stop_job to stop the job in it's
> > tracks. Something like
> > if datepart(hh, getdate()) = 0 and datepart(mi, getdate()) = 0
> > exec sp_Stop_Job 'JobName'
> >
> > If you wanted to escape every hour then remove the "datepart(hh,
> getdate())
> > = 0 and " from the if statement. Besure to schedule the job to start
> every
> > minute of every day, so that when it stops it will automatically restart.
> >
> > --
> > Denny Cherry
> > DBA
> > GameSpy Industries
> > "Lixin Fan" <lixinf@.mailcode.com> wrote in message
> > news:18e9901c386db$09351760$a601280a@.phx.gbl...
> > > Denny,
> > >
> > > Thank you very much.
> > >
> > > Since the Job becomes an endless process, do you think
> > > this will cause some potential issues?
> > >
> > > Lixin
> > >
> > > >--Original Message--
> > > >Change the job step properties, so that when the step
> > > completes, it runs it
> > > >self again, instead of completing successfully.
> > > >
> > > >--
> > > >Denny Cherry
> > > >DBA
> > > >GameSpy Industries
> > > >"Lixin Fan" <lixinf@.mailcode.com> wrote in message
> > > >news:299801c386ce$22c337f0$a001280a@.phx.gbl...
> > > >> Hello,
> > > >>
> > > >> I want to set up one SQL job to continuously run with
> > > >> minimum break. In another word, the second run of the
> > > SQL
> > > >> Job will start as soon as the first run is completed. Is
> > > >> there any way to do it?
> > > >>
> > > >> Thanks a lot,
> > > >> Lixin
> > > >
> > > >
> > > >.
> > > >
> >
> >
>|||You're right, Tibor, it's not necessary if you use SQL Server Agent to
schedule the job, which what we are talking about here. Just a habit of
wanting to take the concurrency matter into my own hands -:)
Linchi Shea
linchi_shea@.NOSPAMml.com
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ubSCczxhDHA.2496@.tk2msftngp13.phx.gbl...
> > Another mehtod to run a job repeatly, especially if you don't need the
loop
> > to be very tight, is to schedule the job to start every minute (or any
other
> > time unit). In the job, using an apllication lock to detect whether the
job
> > is still running. Exit immediately if it is.
> Is that necessary? As far as I understand, Agent will not start a job if
it is already running.
> Yet another option is to start the job at agent startup and use a loop
with in the job (assuming one
> TSQL jobstep). The loop is using a simple WAITFOR construct.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
> news:u1ENtQwhDHA.3276@.tk2msftngp13.phx.gbl...
> > > if datepart(hh, getdate()) = 0 and datepart(mi, getdate()) = 0
> > > exec sp_Stop_Job 'JobName'
> >
> > This may not guarantee that sp_stop_job will always run because the
> > currently running job may not stop before 00:00. It's safer to do a time
> > range check.
> >
> > Another mehtod to run a job repeatly, especially if you don't need the
loop
> > to be very tight, is to schedule the job to start every minute (or any
other
> > time unit). In the job, using an apllication lock to detect whether the
job
> > is still running. Exit immediately if it is.
> >
> > --
> > Linchi Shea
> > linchi_shea@.NOSPAMml.com
> >
> >
> > "Denny" <mrdenny@.gamespy.com> wrote in message
> > news:eKtYe4uhDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > > It "shouldn't". I've run a job in an endless loop before. I would be
an
> > > escape clause in there every day, or every hour so that it exits, and
> > writes
> > > the job history to disk. You can use sp_stop_job to stop the job in
it's
> > > tracks. Something like
> > > if datepart(hh, getdate()) = 0 and datepart(mi, getdate()) = 0
> > > exec sp_Stop_Job 'JobName'
> > >
> > > If you wanted to escape every hour then remove the "datepart(hh,
> > getdate())
> > > = 0 and " from the if statement. Besure to schedule the job to start
> > every
> > > minute of every day, so that when it stops it will automatically
restart.
> > >
> > > --
> > > Denny Cherry
> > > DBA
> > > GameSpy Industries
> > > "Lixin Fan" <lixinf@.mailcode.com> wrote in message
> > > news:18e9901c386db$09351760$a601280a@.phx.gbl...
> > > > Denny,
> > > >
> > > > Thank you very much.
> > > >
> > > > Since the Job becomes an endless process, do you think
> > > > this will cause some potential issues?
> > > >
> > > > Lixin
> > > >
> > > > >--Original Message--
> > > > >Change the job step properties, so that when the step
> > > > completes, it runs it
> > > > >self again, instead of completing successfully.
> > > > >
> > > > >--
> > > > >Denny Cherry
> > > > >DBA
> > > > >GameSpy Industries
> > > > >"Lixin Fan" <lixinf@.mailcode.com> wrote in message
> > > > >news:299801c386ce$22c337f0$a001280a@.phx.gbl...
> > > > >> Hello,
> > > > >>
> > > > >> I want to set up one SQL job to continuously run with
> > > > >> minimum break. In another word, the second run of the
> > > > SQL
> > > > >> Job will start as soon as the first run is completed. Is
> > > > >> there any way to do it?
> > > > >>
> > > > >> Thanks a lot,
> > > > >> Lixin
> > > > >
> > > > >
> > > > >.
> > > > >
> > >
> > >
> >
> >
>|||> Just a habit of
> wanting to take the concurrency matter into my own hands -:)
And a good habit it is. I wonder whether I should say that I found out the behavior by... chance...?
:-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:el$YUr0hDHA.3204@.TK2MSFTNGP11.phx.gbl...
> You're right, Tibor, it's not necessary if you use SQL Server Agent to
> schedule the job, which what we are talking about here. Just a habit of
> wanting to take the concurrency matter into my own hands -:)
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:ubSCczxhDHA.2496@.tk2msftngp13.phx.gbl...
> > > Another mehtod to run a job repeatly, especially if you don't need the
> loop
> > > to be very tight, is to schedule the job to start every minute (or any
> other
> > > time unit). In the job, using an apllication lock to detect whether the
> job
> > > is still running. Exit immediately if it is.
> >
> > Is that necessary? As far as I understand, Agent will not start a job if
> it is already running.
> > Yet another option is to start the job at agent startup and use a loop
> with in the job (assuming one
> > TSQL jobstep). The loop is using a simple WAITFOR construct.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
> > news:u1ENtQwhDHA.3276@.tk2msftngp13.phx.gbl...
> > > > if datepart(hh, getdate()) = 0 and datepart(mi, getdate()) = 0
> > > > exec sp_Stop_Job 'JobName'
> > >
> > > This may not guarantee that sp_stop_job will always run because the
> > > currently running job may not stop before 00:00. It's safer to do a time
> > > range check.
> > >
> > > Another mehtod to run a job repeatly, especially if you don't need the
> loop
> > > to be very tight, is to schedule the job to start every minute (or any
> other
> > > time unit). In the job, using an apllication lock to detect whether the
> job
> > > is still running. Exit immediately if it is.
> > >
> > > --
> > > Linchi Shea
> > > linchi_shea@.NOSPAMml.com
> > >
> > >
> > > "Denny" <mrdenny@.gamespy.com> wrote in message
> > > news:eKtYe4uhDHA.2448@.TK2MSFTNGP12.phx.gbl...
> > > > It "shouldn't". I've run a job in an endless loop before. I would be
> an
> > > > escape clause in there every day, or every hour so that it exits, and
> > > writes
> > > > the job history to disk. You can use sp_stop_job to stop the job in
> it's
> > > > tracks. Something like
> > > > if datepart(hh, getdate()) = 0 and datepart(mi, getdate()) = 0
> > > > exec sp_Stop_Job 'JobName'
> > > >
> > > > If you wanted to escape every hour then remove the "datepart(hh,
> > > getdate())
> > > > = 0 and " from the if statement. Besure to schedule the job to start
> > > every
> > > > minute of every day, so that when it stops it will automatically
> restart.
> > > >
> > > > --
> > > > Denny Cherry
> > > > DBA
> > > > GameSpy Industries
> > > > "Lixin Fan" <lixinf@.mailcode.com> wrote in message
> > > > news:18e9901c386db$09351760$a601280a@.phx.gbl...
> > > > > Denny,
> > > > >
> > > > > Thank you very much.
> > > > >
> > > > > Since the Job becomes an endless process, do you think
> > > > > this will cause some potential issues?
> > > > >
> > > > > Lixin
> > > > >
> > > > > >--Original Message--
> > > > > >Change the job step properties, so that when the step
> > > > > completes, it runs it
> > > > > >self again, instead of completing successfully.
> > > > > >
> > > > > >--
> > > > > >Denny Cherry
> > > > > >DBA
> > > > > >GameSpy Industries
> > > > > >"Lixin Fan" <lixinf@.mailcode.com> wrote in message
> > > > > >news:299801c386ce$22c337f0$a001280a@.phx.gbl...
> > > > > >> Hello,
> > > > > >>
> > > > > >> I want to set up one SQL job to continuously run with
> > > > > >> minimum break. In another word, the second run of the
> > > > > SQL
> > > > > >> Job will start as soon as the first run is completed. Is
> > > > > >> there any way to do it?
> > > > > >>
> > > > > >> Thanks a lot,
> > > > > >> Lixin
> > > > > >
> > > > > >
> > > > > >.
> > > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||>--Original Message--
>It "shouldn't". I've run a job in an endless loop
before. I would be an
>escape clause in there every day, or every hour so that
it exits, and writes
>the job history to disk.
Can you explain why the Job must exit to write history
into disk?
The Job I use has 16 steps. I set up the last step goes
back to the first step when the last step succeeds. I can
view the Job history for each step in EM and
msdb..syshistory table while the Job is endlessly running.
Do you mean that the job history is still only in the
memory instead of disk until I stop the Job?
Thanks,
Lixin|||Yes, you can view each step as that step completes. I've just notices that
if you keep a job running non-stop for a few days, the server can start to
get annoyed with you. Stopping the job every once and a while seams to keep
it from getting to upset.
--
Denny Cherry
DBA
GameSpy Industries
"Lixin Fan" <lixinf@.mailcode.com> wrote in message
news:00a101c3875f$829d5de0$a101280a@.phx.gbl...
> >--Original Message--
> >It "shouldn't". I've run a job in an endless loop
> before. I would be an
> >escape clause in there every day, or every hour so that
> it exits, and writes
> >the job history to disk.
> Can you explain why the Job must exit to write history
> into disk?
> The Job I use has 16 steps. I set up the last step goes
> back to the first step when the last step succeeds. I can
> view the Job history for each step in EM and
> msdb..syshistory table while the Job is endlessly running.
> Do you mean that the job history is still only in the
> memory instead of disk until I stop the Job?
> Thanks,
> Lixin
>
>