Hi All
is there anyway I can measure how long messages are remaining in sys.transmission_queue for ? Maybe an average figure for a given period ?
many thanks Simon
That wouldn't be real easily done without accounting for things like orphaned or poison messages. It's not too hard to get an idea of the activity though as you can get the messages enqueued and dequeued with Performance Monitor. You can get totals and per second. Check the objects listed for service broker in this document:
SQL Server, Broker Statistics Object
If you think you are having issues with your queue, this is a pretty good monitoring script that detects a lot of the issues:
Monitoring Service Broker
Sue