A Stored Procedure that must be runnig for all day long everyday.

A Stored Procedure that must be runnig for all day long everyday.

Post by Vincent Qing Y » Wed, 11 Dec 1996 04:00:00




# >
# >I have a Stored Procedure that must be runnig for all day long everyday.
# >Which is the best way?
#
# I would be inclined to schedule and call your stored
# procedure from Unix.  The danger here is if one invocation hasn't
# completed when the next one starts.  You'd need to determine whether
# or not that matters to you or put code in the sp to detect that
# situation.

Well, for every advantage, there's a disadvantage:)

The problem with the "waitfor" method is that it's OK only if it indeed
lives forever.  But the proc could die for a number of reasons:
    - It could be a deadlock victim.
    - The table accessed by the proc changed schema (e.g., a new
      index is created for the table while the proc is running.)
    - The proc updates a record, and the update fails.
    - Some DBA simply killed the proc unintentionally.

Those are not too unlikely for a "long live" proc.

So if you use waitfor to loop forever, you probably still need
a periodic cron job to see whether the proc is still alive.  Therefore
we are back to the crontab solution after all.

--

Q Vincent Yin               |   Repeat

(604) 876-9096 (H)          |   Until 0 = 1;

 
 
 

A Stored Procedure that must be runnig for all day long everyday.

Post by m » Thu, 12 Dec 1996 04:00:00



>Hey ALL,

>I have a Stored Procedure that must be runnig for all day long everyday.
>Which is the best way?

>1) a scheduled unix scrip that starts every 60 seconds and
>calls my proc (isql -Uxx -P -iMyScrip ) and then finish.

>2) a Stored Procedure started once a time (by SA? )running for ever
>(is that possible? How?) that do something and then enters in a sleep
>mode for 60 seconds (waitfor delay "00:00:60") and then repeat that
>process.

>Thanks in advance,
>                    Andre Fonseca

No matter what you do, I'd put it in a stored procedure just as a good
programming practice.  I'm not sure what the overhead with waitfor is
but SQL Server still has to poll all processes even if they're not
active.  I would be inclined to schedule and call your stored
procedure from Unix.  The danger here is if one invocation hasn't
completed when the next one starts.  You'd need to determine whether
or not that matters to you or put code in the sp to detect that
situation.

Mike

 
 
 

A Stored Procedure that must be runnig for all day long everyday.

Post by Ad van Dij » Thu, 12 Dec 1996 04:00:00


If you would like to check if your prevoious execution has finished (using
cron), perhaps this is something:

- check if temporary table exists

- create a temporary table in the stored procedure to lock out the next one
(create #ZZZZZ(col1 int)

- if stored-procedure ends, temporary table is gone

check:
if exists (select * from tempdb..sysobjects where name like "%ZZZZZ%"
begin
        return
end

I've used this to make sure some stored procedures don't run more than
once.

--

Ad van Dijk

DBA Dutch Ministry of Finance, Taxes

I specifically DO NOT give anyone permission to use my email adress for any
commercial or non commercial mailings. I will bill everyone who sends me
this kind of mail for wasting my time. Under Dutch law,  people who don't
let me know they diasagree with such a bill are obliged to pay it.

 
 
 

A Stored Procedure that must be runnig for all day long everyday.

Post by Vincent Qing Y » Fri, 13 Dec 1996 04:00:00



# If you would like to check if your prevoious execution has finished (using
# cron), perhaps this is something:
#
# - check if temporary table exists
#
# - create a temporary table in the stored procedure to lock out the next one
# (create #ZZZZZ(col1 int)
#
# - if stored-procedure ends, temporary table is gone
#
# check:
# if exists (select * from tempdb..sysobjects where name like "%ZZZZZ%"
# begin
#       return
# end

It doesn't need to be that complicated.  You don't need to create any object.
Just do something like:

        if 1 < (select count(*) from master..sysprocesses
                   where command like "%my_proc%")
            return

--

Q Vincent Yin               |   Repeat

(604) 876-9096 (H)          |   Until 0 = 1;

 
 
 

A Stored Procedure that must be runnig for all day long everyday.

Post by DunnJ » Tue, 24 Dec 1996 04:00:00


Hello,

I would suggest that the best solution to this is a Unix daemon that
wakes up every 60 seconds, connects to the server and runs the
stored procedure.  The resources that are used by maintaining a
a process that sleeps for 60 seconds seems excessive. Not to mention
what to do if the server bombs the process out (ever hear of process
infected with 10 or 11??)  Using cron is a good solution, but not a
trivial
amount of start up work every 60 seconds, starting a new process, etc,
etc.
what happens when you run out of Unix processes and cron can't start
it??  by having a daemon, you could have root kick it off when the host is
booted and it stays running until the host is shutdown.
cron is well suited for processes that need to run every 10 or 15 minutes
but
every minute is excessive, a daemon a better choice. Personally, BTW, I
would leave the connection up in the daemon, since opening a connection
every 60 seconds is not a trivial task for the server (it has to
reallocate all
those resources every minute.) the advantage of the daemon process is that
its death can be detected and started up very easily.

have fun!

Joe