Why the sudden increase of my database .mdf file size?

Question

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- NULL
• dbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_Sender has 0 row is_retention_enabled 0 and Activation procedure [dbo].[dbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_QueueActivationSender]

Answer 1

Messages will be retained in Service Broker queues until they are consumed with RECEIVE or the associated conversations ended with END CONVERSATION. Messages will also be retained in the queue after RECEIVE until the conversation is ended if the queue is set to to RETENTION = 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.

SELECT
      sch.name
    , sq.name
    , p.rows
    , sq.is_receive_enabled
    , sq.is_enqueue_enabled
    , sq.is_retention_enabled
    , sq.is_activation_enabled
    , sq.activation_procedure
FROM sys.service_queues AS sq
JOIN sys.schemas AS sch ON sch.schema_id = sq.schema_id
JOIN sys.internal_tables AS it ON it.parent_object_id = sq.object_id
JOIN sys.partitions AS p ON p.object_id = it.object_id
WHERE p.index_id = 1;

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.

SET NOCOUNT ON;
DECLARE 
      @conversation_handle uniqueidentifier
    , @BatchSsize int = 100000
    , @MessagesDeleted int = -1;
WHILE @MessagesDeleted <> 0
BEGIN
    RECEIVE TOP (@BatchSsize) @conversation_handle = conversation_handle
        FROM [dbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_Receiver];
    SET @MessagesDeleted = @@ROWCOUNT;
    RAISERROR('%d messages deleted this batch', 0, 0, @MessagesDeleted) WITH NOWAIT;
END;
GO