Posted by: sbelus | 22/01/2016

Quartz.NET – How to fire trigger NOW

Introduction

In one of my project I have Quartz.NET job scheduler with a job scheduled once per day (at night). Generally it is an SQL script, which is fired from C# code in a loop several times with different parameters. Unfortunately sometimes it fails.

When it fails I need to fix it and most likely lunch it again. The problem is that I can’t change cron for the job and restart the scheduler just like that. The scheduler works as Windows Service on remote server. Well, eventually the job will fire next day, but sometimes I need to run it as soon as possible. The only solution for me is to modify data in quartz SQL tables.

Solution

I found a column NEXT_FIRE_TIME in QRTZ_TRRIGERS table which is a bigint data type and determines next fire time (what a surprise 🙂 ) of a trigger expressed in ticks. The date can be retrieved by sample sql code (thanks to this answer) . I can list all triggers and their next fire time in human readable datetime data type (Download script)

SELECT TRIGGER_NAME
,NEXT_FIRE_TIME
,CAST(NEXT_FIRE_TIME / 864000000000.0 - 693595.0 AS DATETIME) + (GETDATE() - GETUTCDATE()) NEXT_FIRE_DATE
FROM QRTZ_TRIGGERS
ORDER BY TRIGGER_NAME

As Quartz.NET is periodically quering for NEXT_FIRE_TIME before it run the job, the real problem is to put there proper value. And here comes another help from www.ketek.ro.

Converting date and time to ticks with some modifications looks like this:(Download script)

DECLARE @ticksPerDay BIGINT = 864000000000 -- DO NOT CHANGE
DECLARE @date DATETIME
DECLARE @triggerName varchar(300)

--####### set the source date value here ########
SET @date = GETUTCDATE() --put here UTC date
SET @triggerName = 'triggerName'
--#####################################
DECLARE @date2 DATETIME2 = @date
DECLARE @dateBinary BINARY (9) = cast(reverse(cast(@date2 AS BINARY (9))) AS BINARY (9))
DECLARE @days BIGINT = cast(substring(@dateBinary, 1, 3) AS BIGINT)
DECLARE @time BIGINT = cast(substring(@dateBinary, 4, 5) AS BIGINT)
DECLARE @nextFireTime bigint = @days * @ticksPerDay + @time

SELECT @date AS [DateTime]
,@nextFireTime AS [Ticks]
,CAST((@nextFireTime) / 864000000000.0 - 693595.0 AS DATETIME) + (GETDATE() - GETUTCDATE()) as [CheckDate] --local time (for SQL SERVER)

UPDATE QRTZ_TRIGGERS
SET NEXT_FIRE_TIME = @nextFireTime
WHERE TRIGGER_NAME= @triggerName

I hope it would be useful for you.

ps. sorry for external links for SQL scripts. I need to think about moving this blog to other server

Advertisements

Responses

  1. This is very helpful. Thanks Szymon.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: