PRB: Autostart Task Fails and Does Not Restart Automatically (197096)

SYMPTOMS

If a scheduled task is running in Autostart mode, it should start automatically when the SQLExecutive service is started. However, if a transient or network error causes the task to stop or fail, it is not automatically restarted. This is the expected behavior and this article addresses methods to restart such tasks automatically.

WORKAROUND

Simply restarting the task is not always a solution to the problem. In cases where the real problem is transient (such as a network error or a deadlock), a restart would help. However, in other cases, the failure may occur because of a problem that requires manual intervention. The following workarounds discuss how you can automatically restart an auto-start type task.

To work around this problem and restart the task, do one of the following:

Change the scheduled task to use a recurring schedule. For example, change it so that it runs every 1 minute of every 1 day. This means that, if it fails, the task will be started at the next one minute mark. In the case of transient errors, this will fix the problem. Also, note that this workaround may increase the latency for the task.
Set up an alert to restart the task in the event of a failure. Note that this method requires one alert per task. Also, you cannot set up these alerts by using the SQL Enterprise Manager UI because, in the "Response Definition" part, it only lists tasks that are not set to Autostart. To set up these alerts, use the sp_addalert stored procedure. For a sample script using this method, see the MORE INFORMATION section of this article.

MORE INFORMATION

The following is the sample script for using sp_addalert to configure an alert to restart the task if a failure occurs:

   use msdb
   go
   exec msdb..sp_addalert @name = 'A', @message_id = B,  @enabled = 1,
   @delay_between_responses = 10, @database_name = 'C',
   @notification_message = null, @task_name = 'D',
   @event_description_keyword = 'E', @include_event_description_in
   = 'none', @raise_snmp_trap = 0

The following are the definitions for the parameters used above:

   Parameter variable   Description
   ----------------------------------------------------------------------

   A                    Name of the alert
   B                    Error number reported by the task on failure
   C                    Database name
   D                    Name of the task to restart
   E                    Key words you want to use from the error message

NOTES:

The delay_between_response is the wait period that the alert waits before firing again. You will need to decide what wait period will work best for you. Remember that these alerts are being added to your Windows NT Event Viewer log. If a restart does not correct the problem, the delay_between_response may not be set high enough, and may start filling up your Event Viewer log.
If you need to edit the alert, do not edit it in the SQL Enterprise Manager UI, because doing so will result in saving the alert with no task name. Instead, use the sp_updatealert stored procedure if you need to edit the alert.