Author |
Topic |
cindylee
Yak Posting Veteran
55 Posts |
Posted - 2005-02-18 : 00:38:35
|
Hi guys,I have noticed a job runs for a long time(rarely) than normal time.Is there anyway to automatically stop a Job after 15 mins.I dont want a particular job to run more than 15 mins cos most of the time it just takes 1 min but once in a while it runs forever until i identify and manually stop it.any help/suggestionsCindy |
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-18 : 04:16:26
|
Easiest way is to include logging in the job.Add an insert into the step into a log table which inserts the start time and spid. Add another at the end with the end time.Have another job which periodically checks this table and if there is no end within the alloted time it checks the spid for the start time and command being executed ( see http://www.mindsdoor.net/SQLAdmin/sp_nrInfo.html for getting the command from dbcc inputbuffer) and if it is the offending job then kills it.You can do a similar thing by just checking the spids for the command, adding an initial step and checking msdb..sysjobstephistory, or using sp_help_jobs==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-02-18 : 06:55:20
|
also, you may add into the job this simple activescript (as the job's 1st step):Dim oSet o = CreateObject ("WScript.Shell")o.Run "D:\jobchecker.vbs", 0, FalseSet o = Nothingand this step will invoke the presaved "standalone" VBS script (D:\jobchecker.vbs):Dim s, j, tSet s = CreateObject("SQLDMO.SQLServer")Set j = CreateObject("SQLDMO.Job")s.Connect "SqlServerName"Set j = s.JobServer.Jobs("myJobName")t = TimerWhile Timer - t < 15 * 60WendIf j.CurrentRunStatus <> 4 Thenj.StopEnd IfSet j = Nothings.DisconnectSet s = Nothing |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-02-18 : 07:13:29
|
better firstly time delay, then connecting:Dim s, j, tt = TimerWhile Timer - t < 15 * 60WendSet s = CreateObject("SQLDMO.SQLServer")Set j = CreateObject("SQLDMO.Job")s.Connect "SqlServerName"Set j = s.JobServer.Jobs("myJobName")If j.CurrentRunStatus <> 4 Thenj.StopEnd IfSet j = Nothings.DisconnectSet s = Nothing |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-02-18 : 15:18:35
|
I tested it: works fine.But what if the job is fired more often than once per each 15 minutes?ps: there is Job's property .LastRunTime. |
|
|
cindylee
Yak Posting Veteran
55 Posts |
Posted - 2005-02-20 : 18:01:34
|
Thanks NR and Stoad. will try today and let you know..Cindy |
|
|
woodybarry
Starting Member
4 Posts |
Posted - 2005-02-23 : 09:02:49
|
Thanks for everybody. The code is very helpful. I have a job running every 3 minutes 24 hours a day. The job only lasts 1 minute. There is a separate external application in the job that causes random hang. How to kill the job? Can I just check the LastRunTime, if it is more than 6 minutes, then stop the job? Will the job be restarted? I am going to do a test. I'll let everybody know. |
|
|
woodybarry
Starting Member
4 Posts |
Posted - 2005-02-25 : 10:38:42
|
I had a hard time to compare current time to LastRunTime which is a long number.Lnumber=hour(now) *10000+minute(now)*100--LastRunTime is a Long number, more than 6 minutes ago, stop the jobif (Lnumber- j.LastRunTime) >600 then j.StopEnd IfIt seems something wrong with j.LastRunTime |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-02-25 : 15:54:58
|
lol, woodybarry, I suggest this very cunning workaround!Dim s, j, t, jd, jtSet s = CreateObject("SQLDMO.SQLServer")Set j = CreateObject("SQLDMO.Job")s.LoginSecure = Trues.Connect "." ''', "sa", "pwd"Set j = s.JobServer.Jobs("myJobName")jd = j.LastRunDatejt = j.LastRunTimes.Disconnectt = TimerWhile Timer - t < 6 * 60Wends.Connect "." ''', "sa", "pwd"Set j = s.JobServer.Jobs("myJobName")If j.CurrentRunStatus <> 4 ThenIf j.LastRunDate & j.LastRunTime = jd & jt Thenj.StopEnd IfEnd IfSet j = Nothings.DisconnectSet s = Nothing |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-02-26 : 05:05:16
|
woodybarry;1.Btw, LastRunTime(Date) refers to the last job which has already completed,no matter with failure or with success (I'm not sure whether you noticed it).2.Of course, all VBS date-time functions work absolutely correctly. Look at:jd = j.LastRunDate ''' e.g., = "20050225", i.e., Feb 25, 2005jt = j.LastRunTime ''' e.g., = "194855", i.e., 19:48:55dt = Mid(jd, 5, 2) & "/" & Right(jd, 2) & "/" & Left(jd, 4)tm = Left(jt, 2) & ":" & Mid(jt, 3, 2) & ":" & Right(jt, 2)If DateDiff("n", CDate(dt & " " & tm), Now()) >= 6 Thenj.StopEnd IfLooks fine but it will not work correctly in the most general cases! E.g.,suppose, the previous job was running for 4 minutes. And now it's completedbut its instance of jobchecker.vbs is still running! ... and it can kill a "good" job!... or not?lol, I must admit it is a bit headbreaking for me to examine all possible cases. |
|
|
woodybarry
Starting Member
4 Posts |
Posted - 2005-03-09 : 13:56:40
|
The codes did not work. It seems you can not stop a job in another job. Since my job runs frequently, I guess the only thing works is to insert a record with job process ID+ timestamp at the beginning of the job, and delete the records at the end of the job.Create another job check this record, if now-timestamp > 15 minutes,kill ProcessID. |
|
|
woodybarry
Starting Member
4 Posts |
Posted - 2005-03-09 : 15:01:34
|
quote: Originally posted by woodybarry The codes did not work. It seems you can not stop a job in another job. Since my job runs frequently, I guess the only thing works is to insert a record with job process ID+ timestamp at the beginning of the job, and delete the records at the end of the job.Create another job check this record, if now-timestamp > 15 minutes,kill ProcessID.
This did not work out either. My hanging job just loop a timer, The error message is,Server: Msg 6106, Level 16, State 1, Line 1Process ID 56 is not an active process ID |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2005-03-15 : 14:31:02
|
quote: Originally posted by woodybarry The codes did not work. It seems you can not stop a job in another job.
Stop a job in another job??? I don't like this idea at all.I suggested to stop a job from an external VBS script which is fired by the job in its 1st step (and this 1st step takes only a fraction of second to be executed).Also note, not all jobs can be stopped by conventional means of sql server. Again my recent "silly" sample:exec master..xp_cmdshell 'calc.exe', no_outputxp_cmdshell works in SYNC mode but can't control OS process "calc.exe". |
|
|
|