Take the 2-minute tour ×
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.

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 at 5:32
    
I apply the solution based on this link : rusanu.com/2010/03/09/dealing-with-large-queues –  cici Jun 25 at 6:39
1  
Have the messages in the queue been processed? Is retention turned on? –  mrdenny Jun 25 at 9:20
2  
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 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 at 13:13

1 Answer 1

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

WAITFOR ( RECEIVE TOP ( 10000) 
                   CONVERT(XML, CONVERT(NVARCHAR(MAX), message_body))                  
                  FROM dbo.[EventNotificationQueue] 

> --into @message_body

                  ), TIMEOUT 1000      
share|improve this answer

Your Answer

 
discard

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.