Introduction
This document provides a detailed procedure for diagnosing and resolving the issue of a SQL Server queue that automatically disables itself after being re-enabled. This behavior is often caused by poison messages—problematic messages that lead to repeated failures in the associated service or application. The document outlines the steps to identify, clean up such poison messages, and re-enable the queue to ensure its smooth operation.
Procedure
Identifying the Issue
- Symptom: The queue gets disabled automatically when you try to enable it.
- Likely Cause: A poison message exists in the queue, causing it to disable for protection.
Steps to Resolve
Check the Queue for the Problematic Conversation Handle
- Right-click on the queue in SQL Server Management Studio (SSMS).
- Select “Select Top 1000 Rows”to query the messages in the queue.
- Identify the conversation handle associated with the poison message.
Clean Up the Poison Message
- Use the following query to clean up the conversation related to the poison message
use <Database_Name>
go
end conversation ‘30521592-05A0-EF11-BA88-021*********’ with cleanup
Replace ‘30521592-05A0-EF11-BA88-021*********’ with the actual conversation handle identified in Step 1.
Re-Enable the Queue
- After clearing the poison message, re-enable the queue using the following query
ALTER QUEUE YourQueueName WITH STATUS = ON, ACTIVATION (STATUS = ON);
Replace YourQueueName with the name of your queue.
Conclusion
The issue of a queue automatically disabling itself is typically caused by poison messages. By identifying the problematic conversation handle and using the END CONVERSATION … WITH CLEANUP command, the poison message can be safely removed. After cleanup, the queue can be re-enabled using the ALTER QUEUE statement. Following this procedure ensures the smooth operation of the SQL Server queue without unexpected disabling behavior.
