Mittwoch, 27. Februar 2013 23:04
After Migrating our Production database from sql 2008 to Sql 2012 SPI, our service broker queues are showing errors in the event logs. Prior to the upgrade we were not seeing any errors. We can determine that the queues are working, but are wondering why we are seeing these errors all of a sudden? I have found other postings where people have seen the same problem after the upgrade.
Here is an example of the errors we are seeing.
An error occurred in Service Broker internal activation while trying to scan the user queue 'XX' for its status. Error: 1222, State: 51. Lock request time out period exceeded. This is an informational message only. No user action is required.
Donnerstag, 28. Februar 2013 08:09Moderator
When SQL Server upgraded to a higher version, conversations continue to operate as they did as before, but the system objects are built to support conversation priorities:
•The upgrade process builds the new system objects required to support conversation priorities. It adds conversation priority columns to existing system tables, views, trace events, and performance counters.
•The HONOR_BROKER_PRIORITY database option is initialized to the default of OFF.
•All existing messages in service queues have their priority level set to 10. This means they will be the first messages retrieved by RECEIVE statements.
•All conversation endpoints in the upgraded database are assigned the default conversation priority of 5.
You can start to use conversation priorities in an upgraded database by doing the following:
•Using the ALTER DATABASE statement to set the HONOR_BROKER_PRIORITY database option ON.
•Using the CREATE BROKER PRIORITY statement to define a set of conversation priorities in the database.
TechNet Community Support
- Als Antwort markiert Iric WenModerator Donnerstag, 7. März 2013 08:51
Freitag, 22. März 2013 21:28
Could you provide some insight into how this solution actually applies to the error? Is the implication that since the conversations all have the same priority that locking would occur, and lead to the timeouts? And if so, how does this answer negate that?
I'm running into the same error message, but our service broker implementation is all based on the SqlDependency object in a .NET RIA web service and is solely for query notifications to work when database lookups are modified. I don't believe we have any control over setting conversation priorities in this scenario, but I could be mistaken.