Sql-server – High Service Broker queue

service-brokersql serversql-server-2008

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.

Best Answer

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
Related Question