TempDB internal space usage slowly growing from service broker

Question

Am running SQL Server 2012 (11.0.6540.0) in a AO high availability group with one other server.

tempDB usage usually sits around 819MB (averaged over a week from 2 weeks ago) but since we did a fail-over and patch cycle the tempDB started growing gradually (no DDL changes have been made in this time). We've just done a test fail-over and moved the tempDB off the joint SAN drive that both machines have access to, (we don't want one piece of hardware that can bring down both our primary and secondary replica).

The most recent failover and Instance restart (to move the tempDB) saw the tempDB usage jump to 6348MB used and its slowly growing.

The growth is all on the internal objects which can be seen using:

SELECT   top 5 session_id, request_id, 
         SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count
FROM     sys.dm_db_task_space_usage 
GROUP BY session_id, request_id
ORDER BY request_internal_objects_alloc_page_count DESC

Looking into it, it all is running from spid 35 and 32 which are both service broker related.

I've had a look around and there's talk about this being due to having a WITH CLEANUP on an end conversation but the word cleanup only ever appears in comments so I'm confident that this isn't the issue.

When the instance came back up and took over the primary node the service broker wasn't working so I needed to cycle all of the queues to disabled then enabled state (we've had to do this once before a a year ago but didn't see this issue).

You can see here the use on tempDB as it currently stands over the past 4 days. the drop off this morning is the instance restart and fail-overs.

Does anyone know what I'm missing to get this back under control.

Answer 1

TLDR; Check for conversations being left open completely.

In our system we re-use conversations and have a table dedicated to holding these conversations that are usable, however the dev team setup a new service broker without my knowledge ages ago while I was off, didn't set up these conversation points and didn't set any thresholds on the alerting.

When the new system has been turned on the conversations are being opened but not closed properly and since there aren't any in the pool it is just creating a new conversation (we reached 7.1 million conversations for one service broker)

My steps for fixing was to create and record the 20 conversation handlers that I require for that service broker and record them into our table. This stopped the growth of the tempDB to stop the risk of the DB going down.

Then came the long process of closing off all the un-used conversations

select 
    se.[conversation_handle] 
from 
    sys.conversation_endpoints se with (nolock) 
    left outer join [queue].[maintained_Conversations] qh with (nolock) on qh.[conversation_handle] = se.[conversation_handle]
where 
    qh.service_name is not null 
    and se.far_service = @service

Gives the list of all the IDs cursor through those values and simply run END CONVERSATION @id; on each of them.

After the process has finished the temp DB space will loosen up (Note it does not do it as you close them, it seems to do it in large chunks when you're no longer working on creating / ending them (I cant guarantee this is how it works, just what I observed after stopping mid process and tempDB getting some space back)