Time-limit jobs and stop them automatically

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Time-limit jobs and stop them automatically

Author

Topic

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-15 : 09:12:19

Add into your job you want to time-limit and stop automatically
the following Active Script (VBS) as the job's 1st step:
================================================
job_killer_script = "D:\job_killer.vbs"
this_job_name = "myJob"
time_limit_secs = "20"
os_process_to_kill = "N\A"
Set o = CreateObject("WScript.Shell")
o.Run """" & job_killer_script & """ """ & this_job_name & """ " & _
time_limit_secs & " """ & Now & """ """ & os_process_to_kill & """"
Set o = Nothing
================================================
In red the only parameters you need to change. That's all.

This is D:\job_killer.vbs script which is invoked (asynchronously) from the job's 1st,
controlling, step. It's ready to use and you need no to change anything in it. Only
check its colored lines. And, of course, this script can be invoked simultaneously by
any number of different started jobs. Hope, kill.exe (rkill.exe in win xp) is in its right
place, i.e., in %SystemRoot% folder.
================================================
Option Explicit
On Error Resume Next
Dim o, s, j, jd, jt
Dim job_name, time_limit_secs, step1_dt, os_process_to_kill

With WScript
job_name = .Arguments(0)
time_limit_secs = .Arguments(1)
step1_dt = .Arguments(2)
os_process_to_kill = .Arguments(3)
End With

WScript.Sleep 1000 * time_limit_secs

Set s = CreateObject("SQLDMO.SQLServer")
Set j = CreateObject("SQLDMO.Job")
s.LoginSecure = True
s.Connect "."
Set j = s.JobServer.Jobs(job_name)
jd = j.LastRunDate
jt = Right("00000" & j.LastRunTime, 6)
'''this is for mdy system date format:
jd = Mid(jd, 5, 2) & " " & Right(jd, 2) & " " & Left(jd, 4)
'''this is for dmy system date format:
'''jd = Right(jd, 2) & " " & Mid(jd, 5, 2) & " " & Left(jd, 4)
jt = Left(jt, 2) & ":" & Mid(jt, 3, 2) & ":" & Right(jt, 2)
If DateDiff("s", CDate(jd & " " & jt), CDate(step1_dt)) > 5 Then
If os_process_to_kill <> "N\A" Then
Set o = CreateObject("WScript.Shell")
o.Run "kill -f " & os_process_to_kill, , True
Set o = Nothing
End If
j.Stop
End If

s.DisConnect
Set j = Nothing
Set s = Nothing
================================================

Sample of usage:
suppose you have a job named "My silly job" with this silly T-SQL step:

exec master..xp_cmdshell 'calc.exe', no_output
waitfor delay '555:55:55'

and you want to time-limit this "job" to 10 seconds of running.
Then parameters of its 1st (controlling) step should be:

job_killer_script = "D:\job_killer.vbs"
this_job_name = "My silly job"
time_limit_secs = "10"
os_process_to_kill = "calc.exe"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources