November 4, 2009 at 1:21 pm
I was faced with a situation where a SQL Job that runs a batch of 5000 records has to restart itself right after it is completed. Setting a schedule(i.e every 10 minutes) would not work because the job execution time varies.
My solution was as follows:
Create a table(job_status) that will hold the results from executing xp_sqlagent_enum_jobs. The value of the STATE field will show the current status..1 = executing 4 = not executing.
Use a trigger on the table to start the SQL Job when the STATE value is 4
BEGIN
WAITFOR DELAY '00:00:10'
IF(select State from job_status WHERE job_id = 'xxxxxxxx')=4
exec msdb.dbo.sp_start_job @job_name='yyyyyy'
END
To populate the table I run a SQL Job that executes every minute
SET NOCOUNT ON
truncate table job_status
insert into job_status
exec master.dbo.xp_sqlagent_enum_jobs 1,t
I added error handling and notifications, for instance if the Job 'YYYY' fails the Job_status job will get disabled and a notification is sent.
November 29, 2009 at 12:40 pm
You need to implement a control process to know when there are pending records to process,
Something like create a control tables to know when there is a pending process and when the process is running and when the process is done and schedule that job run every minute or continuously.
For example every record to be processed can be have a column status (PENDING, PROCESSING, DONE) and when the record are inserted mark the records in PENDING, when the process is running in PROCESSING and when the process are done in DONE, repeat this process again.
November 29, 2009 at 2:10 pm
I am doing something similar to that by marking the records with a timestamp when the batch starts and a unique process id(created from a date serial function) which is updated every time the stored proc executes for each set of 5000 records.
The process is working well in the production system and it has basically freed up a person from running this manually twice a day, which was extremely time consuming.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply