I have an event set to run every 5 minutes. When I trigger it manually, it works perfectly. But it doesn't seem to want to fire on its own schedule.
The only thing I can think of is that it doesn't finish in 5 minutes, so it doesn't queue itself up for another go.
The procedure the event calls (dba.sp_update_page_table) runs in about 68 seconds when I run it from ISQL. Changing the background priority seems to have no impact on the time.
Do events run at a lower priority than a user connection?
I'm running SQLA 220.127.116.1150
This has nothing to do with your question, but BEWARE the SET OPTION statement.
As coded, this statement
set option background_priority = "on";
permanently changes the option setting for the current user id, which is probably DBA, and that setting will take effect next time that user makes a connection.
I think you might have wanted to do this
set temporary option background_priority = "on";
which sets the option for the duration of the current connection only.
answered 02 Apr '10, 19:20
No offense intended, but how do you know it's not running?
Are there any error messages appearing in the database console log? Events don't have "clients" so that's where exceptions get documented.
I suggest you put at least one MESSAGE ... TO CONSOLE statement in the event to debug.
The following seemed to work OK in 18.104.22.16875; I changed it to a 1 minute interval because I'm impatient, and changed the CALL to a MESSAGE:
CREATE EVENT "e_update_page_id" SCHEDULE "e_update_page_id_1" START TIME '12:02' EVERY 1 MINUTES START DATE '2003-07-07' HANDLER BEGIN set option background_priority = "on"; MESSAGE STRING ( CURRENT TIMESTAMP, ' hello' ) TO CONSOLE; commit; END 2010-04-02 15:05:00.031 hello 2010-04-02 15:06:00.078 hello 2010-04-02 15:07:00.015 hello 2010-04-02 15:08:00.062 hello 2010-04-02 15:09:00.078 hello 2010-04-02 15:10:00.031 hello
answered 02 Apr '10, 19:10