Post reply

Executing a SQL Job based on the Current Job Status

  • November 4, 2009 at 1:21 pm

    #216443

    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

    #1085152

    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.

    SNM

    Try Free Microsoft SQL Server Data Collector & Performance Monitor.

    http://www.analyticsperformance.com[/url]

    @nalyticsperformance - Microsoft SQL Server & Windows Server Free Data Collector

  • November 29, 2009 at 2:10 pm

    #1085163

    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