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.
The fix for the issue is not yet available. In the meantime, you can restart SQL Service to reclaim the space from tempdb.
:-(