Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

My production database suffers from a large Service Broker message queue. It is around 2 million. I would like to decrease it as small as possible.

I have tried dbcc dbreindex('sys.queue_messages_245995913') from the DAC connection, based on the advice given by Remus Rusanu here.

But when I look at the queue properties, the queue size remains the same.

share|improve this question
Why do you think reindexing a system object under DAC would delete rows? – Aaron Bertrand Jun 25 '14 at 5:32
I apply the solution based on this link : – cici Jun 25 '14 at 6:39
Have the messages in the queue been processed? Is retention turned on? – mrdenny Jun 25 '14 at 9:20
Did you verify that the other conditions Remus talks about are also present in your scenario? My guess is you're not operating at that scale, it's something far simpler, and your queue is actually full, not just full of unghosted rows. – Aaron Bertrand Jun 25 '14 at 10:59
This really sounds like it may be an issue with how the messages are being handled. Are you sure conversations are being closed? – JNK Jul 25 '14 at 13:13

TRY TOP 10000 INSTEAD of 1 to quickly read messages from Queue that will clean the queue faster

                   CONVERT(XML, CONVERT(NVARCHAR(MAX), message_body))                  
                  FROM dbo.[EventNotificationQueue] 

> --into @message_body

                  ), TIMEOUT 1000      
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.