Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 clearing all the messages from the service broker

Author  Topic 

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2014-09-09 : 05:03:17
Hi All,

Iam currently trying to remove the messages from the queue if the count is more than 15000 as shown below in the stored procedure :

Declare @CountMBSClientStatusSendQueue BIGINT
SELECT @CountMBSClientStatusSendQueue=rows
From sys.partitions P
Inner Join sys.internal_tables IT On IT.object_id = P.object_id
Where IT.parent_object_id = object_id('MBSClientStatusSendQueue')
And P.index_id In (1, 0)

IF @CountMBSClientStatusSendQueue > 15000
ALTER DATABASE MBS SET NEW_BROKER WITH ROLLBACK IMMEDIATE

But the memory usage of the Sql server was very high and effecting the performance of the sql server. So I would like to know the easiest and the best way to cleanup the messages in the queues after the count is more than 10000

Kindly help me on the same.

Thanks,

Ram




rams
   

- Advertisement -