Troubleshooting Activation Stored Procedures
Activated stored procedures run on a background session. Therefore, the techniques for troubleshooting an activation stored procedure differ slightly from the techniques used for troubleshooting stored procedures that are part of an interactive session.
Technique: Viewing Activation Stored Procedure Output
The Database Engine writes output from an activated stored procedure to the SQL Server error log. If the activated stored procedure is producing incorrect results, or fails to read from the queue, check the SQL Server error log for output from the procedure. Often, the output from the stored procedure provides enough information to locate the problem.
Technique: Running the Stored Procedure From an Interactive Session
One of the best ways to troubleshoot an activation stored procedure is to turn off activation on the queue, and then run the stored procedure from SQL Server Management Studio (or the sqlcmd utility). Running the stored procedure from an interactive session allows you to see any errors returned by the stored procedure.
However, when the stored procedure is activated by the Database Engine, the database settings and security context may be different. Before running the procedure, use EXECUTE AS to set the user for the session to the user specified for activation, and set the options for the session to the database defaults.
For more information, see Internal Activation Context.
Symptom: Activation Stored Procedures Do Not Run
There are several common causes of this symptom.
The settings for the queue may have been changed. Use the catalog view sys.service_queues to confirm the settings for the queue. In particular, check to ensure that activation for the queue is enabled, that the queue specifies the correct stored procedure, and that the queue specifies the correct security principal. Confirm that the security principal has execute permission on the stored procedure.
The stored procedure may be failing to start, or may exit immediately after starting. In this case, check the SQL Server error log for errors from the stored procedure. You can also run the stored procedure from SQL Server Management Studio and check the results.
Symptom: Messages Remain on the Queue
Make sure that activation stored procedures are correctly started:
- Check the dynamic management view sys.dm_broker_queue_monitors to ensure that a queue monitor is active for the queue. If not, activation is not ON for the queue. Use the ALTER QUEUE statement to turn activation ON.
- The state of the queue monitor for the queue should be RECEIVES_OCCURRING. If the queue monitor is not in this state, check the dynamic management view sys.dm_broker_activated_tasks to ensure that activated tasks for the queue are currently running. If there are no activated tasks, then activation is failing. See "Symptom: Activation Stored Procedures Do Not Run," in this section.
If activated tasks are running, but messages remain on the queue, then the task is either failing to RECEIVE, or failing to commit transactions. Check the SQL Server error log for errors from the stored procedure. Stopping activation and running the stored procedure by hand may help to troubleshoot the problem.
See Also
Other Resources
sys.service_queues (Transact-SQL)
sys.dm_broker_queue_monitors
sys.dm_broker_activated_tasks