HOW TO TERMINATE SQL JOBS AFTER CUT-OFF PERIOD

NOTE:

This method should not be attempted on the following jobs:

Ø  Reindex

Ø  Update Statistics

Ø  Replication

Ø  Backup

How many times have we encountered a SQL job which runs for a long time (more than expected) only to error out or even worse hang! 

There is no automated method but this is a long winding method to Schedule jobs on the server which terminate upon reaching a cut off time. Using this method it’s like setting a timeout for the SQL job. Supposing our end objective was to run a stored procedure on SQL server whose execution needs to terminate (when it reaches a pre-decided cut off time), we need to do this:

In SQL server management studio:

CREATE PROC sp_userstoredproc as

Begin

set nocount off

declare @cmd1 varchar(1000),@cmd2 varchar(1000)

set @cmd1 = 'if ''?'' not in (''master'',''model'',''tempdb'',''msdb'') print ''Database Name:?'''

set @cmd2 = 'if ''?'' not in (''master'',''model'',''tempdb'',''msdb'') select [object_id],[name] from [?].sys.objects where type = ''U'''

exec sp_MSforeachdb @command1=@cmd1,@command2=@cmd2

End

We can then schedule this using one the two options:-

1) The Task Scheduler utility (Taskschd.msc)

2) The AT command:  Directions to use the AT command to schedule the above job would be as follows

Start-->run-->cmd.exe

AT \\SERVERNAME 3:00 /every:M,T,W,Th,F sqlcmd -SSERVERNAME\INSTANCENAME -Q"exec sp_userstoredproc" -E

Refer: How to Use the AT Command to Schedule Tasks: http://support.microsoft.com/kb/313565/

In order to set the cut-off time for the job execution we need to navigate to the following dialog box present in the task created.

 

This task would just terminate its connection with the SQL server instance in question when it reaches the cut-off time, which means the SP execution will stop.

Another good script to automatically get an email notification when jobs are running for a long time is given here: http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=LongRunningJobMon

 

Amrutha Varshini J

Support Engineer, Microsoft SQL Server.

Reviewed By

Sudarshan Narasimhan
Technical Lead, Microsoft SQL Server