Runaway SQL Server Agent Job

Hello,

In order to stop an SQL Server Agent job, just use the Enterprise Manager and go to the Management, SQL Server Agent, Jobs folders. Within the "Jobs" folder, right click on the running job and select the "Stop Job" option.

Finding out what is going wrong can be tough. One place you can look for clues is in the application event log of the server. This can be accessed by right-clicking "My Computer", selecting "Manage", then "Event Viewer", then "Application". I've solved a few problems with scheduled jobs this way.

Good luck!

Larry

If you're using the Scheduled Tasks in Windows, there's a checkbox to allow the task to be stopped if it has run for more than user defined number of hours or minutes.

It may also be possible to write a VBscript that is run from a scheduled task. The scheduled VBscript would have to be programmed to look for the Process name. You can get the script to wait for 15 minutes after it begins running by using the wscript.sleep statement. I'm lazy, so rather than calculating how many milliseconds that would be, I'll usually define constants in my scripts for SECONDS = 1000 and MINUTES = 60 * SECONDS. Then, I can create a 15 minute wait by using the statement Wscript.Sleep 15 * MINUTES (much like an Ada program).

After that, it would just be a matter of finding the process and killing the process with the appropriate WMI command.
'-- -----------------
Const SECONDS = 1000
Const MINUTES = 60 * SECONDS

wscript.sleep 15 * MINUTES
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colProcessList = objWMIService.ExecQuery _
("Select * from Win32_Process Where Name = 'SQLSERVERAGENT'")

For Each objProcess in colProcessList
objProcess.Terminate()
Next
'-------------------

Note: if you are going to use a Scheduled Task to do this, remember that the task will have to have the appropriate permissions to run this script.

Also, a great resource for WMI scripting information is http://www.microsoft.com/technet/scriptcenter/ .