Service broker internal spid hogging tempdb

Question

We've recently decommissioned a rather badly implemented service broker setup after a number of near outages, however while doing my morning checks I've noticed that there are 2 Service Broker internal SPIDs (<50) which are still holding ~75% of TempDB space.

Now, I know these are service broker tasks because the CMD column on SysProcesses shows 'BRKR EVENT HNDLR' and 'BRKR TASK', while the last wait types are 'BROKER_EVENTHANDLER' and 'SLEEP_TASK' respectively. Both have 'BACKGROUND' as a status, 0 open trans and a last batch time of over a month ago.

Currently the service broker implementation has been fully disabled (from a code perspective), the queues are disabled (is_activation_enabled and is_receive_enabled = 0), there is nothing in the transmission_queue, conversation_endpoints or actual queues, but the database in question does still have is_broker_enabled = 1 (as do TempDB and MSDB).

The server is currently running SP2 with a security fix (11.0.5343.0) and the TempDB usage appears to persist even after a cluster failover. We've not tried restarting as this is a production system, but I would have thought that a failover would have had the same effect.

Has anyone encountered this issue previously and if so, what did you do to resolve it?

Thanks,

Answer 1

As you can see on this answer, have a look at which databases have service broker enabled (on both target and source servers)

you can use the following query to check which databases have the broker enabled:

--=====================================================================
-- checking what we have and where we point to
--=====================================================================
SELECT @@SERVERNAME
-- my_target_server

USE [master]
GO
SELECT   [name]
 ,[is_broker_enabled] 
 ,[service_broker_guid]
FROM [sys].[databases] 
WHERE 1=1
  AND is_broker_enabled = 1
ORDER BY  NAME
GO

SELECT name,is_broker_enabled,service_broker_guid,
is_db_chaining_on,  is_trustworthy_on FROM sys.databases
    order by 2 desc, 3 desc,  1

I see you mention the security update and patching - well done for applying it!

Depending on the environment we sometimes use db chaining ownership on, and some times trustworthy on, instead of module signing which is the best way in most cases.

anyway, if you are not using the broker, please switch of all of these features for each database, i.e. disable the broker, and switch it off trustworthy and db ownership chain.

--one of my examples of enabling some of these features and renewing the broker id:

  ALTER DATABASE ORCASTG SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE  
  ALTER DATABASE ORCASTG SET NEW_BROKER with rollback immediate
  ALTER DATABASE ORCASTG SET trustworthy on with rollback immediate

You want something like these (provided you are not using any of these features):

  USE MASTER 
  ALTER DATABASE [APCore] SET DISABLE_BROKER WITH ROLLBACK  IMMEDIATE  
  ALTER DATABASE [APCore] SET TRUSTWORTHY OFF WITH ROLLBACK IMMEDIATE
  ALTER DATABASE [APCore] SET DB_CHAINING OFF WITH ROLLBACK IMMEDIATE

and keep monitoring the tempdb usage, I don't think you need neither to restart the service, nor do a failover.

In any case if you are on alwaysOn - Availability Group you may need to remove the database from the availability group before you can disable the broker.