SQL SERVER – MSDB Database Uncontrolled Growth Due to Queue_messages. How to Clear All Messages From a Queue?

Received an email “Need your urgent help On Demand, our MSDB Database has grown too big and we need help to check our MSDB.” I have been an independent consultant for a while and one of the services I provide is “On Demand (50 minutes)” service. This service is very helpful for organizations who are in need immediate help with their performance tuning issue. Though, I have set working ours for my regular clients, every single day, I keep two hours available for this particular offering. This way, I can make sure that anyone who urgently needs my help, can avail the same. Click here to read more about it.

Well, I immediately allocated one of the two hours which I keep available to the individual who sent me an email.

So, I joined call with them and I looked into various table/objects in MSDB. We have found that the objects that are occupying most of the storage are named queue_messages_*

WORKAROND/SOLUTION

The resolution was to delete data from the queue in MSDB. They had no idea who create the queue and how the messages were there. All they were looking for ways to clear the queue. First, we took backup of MSDB database and after that I cleared data from queue_message. You need to make sure that these messages are not important for you. Here is the script which I used.

declare @conversation_handle uniqueidentifier
select top 1 @conversation_handle = conversation_handle from sys.conversation_endpoints
while @@rowcount = 1
begin
     end conversation @conversation_handle with cleanup
     select top 1 @conversation_handle = conversation_handle from sys.conversation_endpoints
end

Well, once we ran above script the database had free up some good amount of space. Though we all know database shrinking is not a good option, this was a good case when we could shrink database and gain additional space. We ran following script on the database and it freed up quite a lot of empty space.

USE [msdb]
GO
DBCC SHRINKDATABASE(N'MSDB')
GO
USE [msdb]
GO
DBCC SHRINKFILE (N'MSDBData' , 0, TRUNCATEONLY)
GO

After running above script, our msdb database which was of 18 GB came down to only 600 MB.

I strongly encourage everyone to read my blog post about SQL SERVER – Shrinking Database is Bad – Increases Fragmentation – Reduces Performance, which explains why shrinking database is not a good idea and how it adversely affect performance. As I said this was a unique scenario where we have to shrink the database.

Well, that’s it. After 50 minutes of consultancy, my client was very happy as we were able to bring back their database to state which made them very happy.

Reference: Pinal Dave (https://blog.sqlauthority.com)