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
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Stop Sql Server Job

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/suggestions

Cindy

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.
Go to Top of Page

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 o
Set o = CreateObject ("WScript.Shell")
o.Run "D:\jobchecker.vbs", 0, False
Set o = Nothing

and this step will invoke the presaved "standalone" VBS script (D:\jobchecker.vbs):

Dim s, j, t
Set s = CreateObject("SQLDMO.SQLServer")
Set j = CreateObject("SQLDMO.Job")
s.Connect "SqlServerName"
Set j = s.JobServer.Jobs("myJobName")

t = Timer
While Timer - t < 15 * 60
Wend

If j.CurrentRunStatus <> 4 Then
j.Stop
End If

Set j = Nothing
s.Disconnect
Set s = Nothing
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-18 : 07:13:29
better firstly time delay, then connecting:

Dim s, j, t

t = Timer
While Timer - t < 15 * 60
Wend

Set s = CreateObject("SQLDMO.SQLServer")
Set j = CreateObject("SQLDMO.Job")
s.Connect "SqlServerName"
Set j = s.JobServer.Jobs("myJobName")

If j.CurrentRunStatus <> 4 Then
j.Stop
End If

Set j = Nothing
s.Disconnect
Set s = Nothing
Go to Top of Page

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.
Go to Top of Page

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

Go to Top of Page

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.
Go to Top of Page

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 job
if (Lnumber- j.LastRunTime) >600 then
j.Stop
End If

It seems something wrong with j.LastRunTime
Go to Top of Page

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, jt
Set s = CreateObject("SQLDMO.SQLServer")
Set j = CreateObject("SQLDMO.Job")
s.LoginSecure = True
s.Connect "." ''', "sa", "pwd"
Set j = s.JobServer.Jobs("myJobName")
jd = j.LastRunDate
jt = j.LastRunTime
s.Disconnect

t = Timer
While Timer - t < 6 * 60
Wend

s.Connect "." ''', "sa", "pwd"
Set j = s.JobServer.Jobs("myJobName")
If j.CurrentRunStatus <> 4 Then
If j.LastRunDate & j.LastRunTime = jd & jt Then
j.Stop
End If

End If

Set j = Nothing
s.Disconnect
Set s = Nothing
Go to Top of Page

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, 2005
jt = j.LastRunTime ''' e.g., = "194855", i.e., 19:48:55

dt = 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 Then
j.Stop
End If

Looks 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 completed
but 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.
Go to Top of Page

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.
Go to Top of Page

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 1
Process ID 56 is not an active process ID
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-15 : 09:22:35
Check my latest solution: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47101
Go to Top of Page

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_output

xp_cmdshell works in SYNC mode but can't control OS process "calc.exe".
Go to Top of Page
   

- Advertisement -