Need to stop SQL Server Agent Job Automatically :

  • Hi,

    Re-Index job runs for more than 10 hrs which affects the production. So decided to stop the job automatically once it runs 10 hrs.

    Can i know how to stop the job automatically after certain amount of time.

  • Hi,

    I do this in my index rebuild job by having a variable that stores the start time of the job. Then a while loop that runs through my indexes, but the condition on the loop says only process an index if the current time is less than 10 hours after the start time.

    The other way you could do it is to use EXEC [msdb].[dbo].[sp_start_job] with a job name, scheduled to run 10 hours after your other job starts.

    Simon



    MCSE: Data Platform
    MCSE: Business Intelligence
    Follow me on Twitter: @WazzTheBadger
    LinkedIn Profile: Simon Osborne

  • This solution allows you to set a duration for how long you want the index job to run. It will finish the index it is working on when it reaches the duration, and then stop the job:

    SQL Server Index and Statistics Maintenance

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply