This method should not be attempted on the following jobs:
Ø Update Statistics
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
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
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
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.
Amrutha Varshini J
Support Engineer, Microsoft SQL Server.
Technical Lead, Microsoft SQL Server