Suddenly my database .mdf file size is increased to more than 250GB but my total table data size count is 4GB only. I did a data shrink, but there are no results. Then I did the query below:
SELECT sch.[name], obj.[name], ISNULL(obj.[type_desc], N'TOTAL:') AS [type_desc],
COUNT(*) AS [ReservedPages],
(COUNT(*) * 8) / 1024.0 / 1024.0 AS [ReservedGB]
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, DEFAULT) pa
INNER JOIN sys.all_objects obj
ON obj.[object_id] = pa.[object_id]
INNER JOIN sys.schemas sch
ON sch.[schema_id] = obj.[schema_id]
GROUP BY GROUPING SETS ((sch.[name], obj.[name], obj.[type_desc]), ())
ORDER BY [ReservedPages] DESC;
and it showed this result:
I have a service broker on.
Any suggestion to remove or how to minimize the queue_message table size?
SELECT COUNT(*) FROM sys.conversation_endpoints;
from here count is 7 and the query in Dan's answer returns 4 activation_procedure and 4 return NULL.
dbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_Receiver
has 361840332 rows,is_retention_enabled
0, Activation procedure- NULLdbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_Sender
has 0 rowis_retention_enabled
0 and Activation procedure[dbo].[dbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_QueueActivationSender]
Best Answer
Messages will be retained in Service Broker queues until they are consumed with
RECEIVE
or the associated conversations ended withEND CONVERSATION
. Messages will also be retained in the queue afterRECEIVE
until the conversation is ended if the queue is set to toRETENTION = ON
.A large number of messages indicates there may be a problem with the application or activated stored procedure where messages are not being dequeued properly. Another possible cause is the queue is disabled.
The query below will return the message counts per queue along with the queue settings to determine next steps.
It seems there is only a single long-running conversation on the queue given the few end points so that rules out conversation leakage as the cause. Also, since there is no activation procedure associated with the problem queue, you should reach out to your app team to determine why the messages aren't being read.
That being said, you can dequeue the messages using a T-SQL script if you know the messages are not actually needed. The example script below does this in batches to avoid filling the log. In the case of full recovery model, make sure the log is large enough to accommodate the messages deleted between log backups.