We have a system that uses Service Broker for asynchronous messaging, with a fairly high throughput of messages. We don't really care about the persistent features of SSB in this scenario -- the messages are pretty much just immediate notifications and if they are not processed immediately when the intended receiver application is up we end up just discarding any backed-up messages when the application restarts.
What we've noticed is that our database is manifesting very large transaction log backups, most of which are filled up with SSB transactions. This is complicating our backup management and test restore scenarios, and generally just making life harder on the DBA side of the fence.
Are there any tips or tricks that can be used to minimize the transaction log overhead of SSB messages in the case where we don't really care about having them available for restore?
DBCC OPENTRAN
or similar? Does thelog_reuse_wait_desc
column in thesys.databases
view show anything of interest? I'm afraid that's all I've got that isn't already in usr's answer.