When Service Broker Fills Your Tempdb: A Real-World SQL Server Incident

Service Broker (SSB) in Microsoft SQL Server is a powerful tool for building asynchronous, reliable messaging applications. However, if not properly managed, it can lead to unintended consequences. One such issue I encountered while working with a customer was unchecked tempdb growth due to internal Service Broker tables, specifically sysdercv and sysdesend, not being cleared as expected.

This article outlines how to identify, understand, and resolve such issues to maintain SQL Server health.

How Service Broker Uses Tempdb

Service Broker manages message processing through dialogs, which store conversation state data. While this data is primarily kept in memory, SQL Server pages the conversation state to tempdb when:

1. There is insufficient memory to store all active conversations.
2. Inactive conversations need to be retained for system consistency.
3. The system cannot determine which dialogs are orphaned and which are valid, leading to their indefinite storage in tempdb.

Why Does Service Broker Store Data in Tempdb?

The conversation data is cached in memory, but when the memory footprint grows beyond a manageable size, Service Broker offloads this data to tempdb. Unlike other SQL Server components that can expire data from cache, Service Broker must persist conversation states until explicitly ended.

• Transmission Objects (TOs): These are small (approximately 1KB) memory structures that store the state of a dialog. They exist in SQL Server's engine memory but are paged to tempdb when memory pressure increases.
• Indefinite Persistence: If a dialog remains open indefinitely due to an application not closing conversations properly, its TOs will be stored in tempdb forever.
• Scaling Concerns: If an application continuously opens dialogs without closing them, the number of TOs will eventually exceed memory capacity, forcing Service Broker to persist them in tempdb, causing unchecked growth.

Connection Between sysdercv, sysdesend, and Tempdb

When Service Broker pages data to tempdb, it primarily interacts with two internal tables: sysdercv (received messages) and sysdesend (sent messages). These tables store message data that must persist until the associated conversation is explicitly ended. Since Service Broker does not differentiate between an inactive but still valid conversation and an orphaned one, all conversation data is retained indefinitely in tempdb unless explicitly removed.

The primary issue arises when:

• A system has a large number of open conversations that are not properly closed.
• Memory pressure forces Service Broker to offload data to tempdb.
• Conversations that should be closed remain in tempdb indefinitely, leading to excessive storage consumption.

Since these tables are not indexed for efficient cleanup at scale, querying them for stale conversations becomes increasingly slow, making manual cleanup extremely difficult.

Identifying the Problem

In this case, the customer's tempdb was consuming excessive disk space despite routine maintenance. Investigation revealed that the internal tables sysdercv (received messages) and sysdesend (sent messages) were not clearing, leading to massive accumulation of conversation state data.

Further analysis showed:

• Table sysdercv contained 10,134,064,966 rows and occupied 5.6 TB (5,640,823 MB).
• Table sysdesend contained 10,134,064,966 rows and occupied 670 GB (669,482 MB).
• The tempdb database reached a peak size of 8 TB.

The root cause was an application bug where conversations were not explicitly closed, leading to the unchecked accumulation of state data in tempdb.

Attempts to Resolve the Issue

Manual Cleanup Attempts

I attempted several cleanup strategies recommended in SQL Server Books Online. They all failed because of the sheer amount of data caused by the lack of proper indexes.

Resetting Service Broker

Another approach was to reset Service Broker. This caused Service Broker to enter a limbo state—assigned a new SID but unable to start correctly. The issue remained unresolved.

Final Solution: Migrating to a New Instance

After multiple failed attempts, the only effective solution was to create a new SQL Server instance and manually migrate only user objects and data, excluding all system-related data. The objective was to ensure a clean installation that did not carry over any lingering Service Broker state from the previous instance.

Migration Steps:

1. Create a new SQL Server instance to ensure a fresh environment.
2. Transfer schema and user data while explicitly avoiding any system objects.
3. Start Service Broker in the new instance with a clean slate, ensuring no lingering conversations from the old system.
4. Monitor message processing to confirm that dialogs were closing properly and tempdb remained under control.

This approach successfully eliminated the uncontrolled tempdb growth, allowing Service Broker to function correctly.

Conclusion

Service Broker provides powerful messaging capabilities but requires careful management to prevent excessive tempdb growth. By proactively monitoring and managing conversation states, SQL Server professionals can avoid performance bottlenecks and ensure long-term system stability.

In this case, after exhausting several cleanup and reset options, migrating to a clean instance was the only viable solution. Adhering to best practices will help prevent such scenarios in the future, keeping Service Broker deployments efficient and manageable.

I hope this was helpful! 💚