Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need to stop SQL Server Agent Job Automatically : Expand / Collapse
Author
Message
Posted Thursday, March 28, 2013 7:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 6:58 AM
Points: 26, Visits: 513
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.
Post #1436473
Posted Thursday, March 28, 2013 9:07 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, October 17, 2014 6:34 AM
Points: 703, Visits: 2,182
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




MCSA: SQL Server 2012
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne
Post #1436543
Posted Sunday, March 31, 2013 5:52 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:06 PM
Points: 7,125, Visits: 12,720
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
Post #1437339
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse