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_Receiverhas 361840332 rows,
is_retention_enabled0, Activation procedure- NULL
dbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_Senderhas 0 row
is_retention_enabled0 and Activation procedure