4

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?

Improve this question
4
  • 1
    I assume you backup the log also in a high frequency?
    – rene
    Dec 28, 2012 at 16:28
  • Yes, every minute.
    – mwigdahl
    Dec 28, 2012 at 22:12
  • Have you checked for long-running transactions with DBCC OPENTRAN or similar? Does the log_reuse_wait_desc column in the sys.databases view show anything of interest? I'm afraid that's all I've got that isn't already in usr's answer.
    – James Lupolt
    Dec 30, 2012 at 16:59
  • Thanks, but yes, we've checked for long-running transactions and there aren't any. As a matter of course, we use a query timeout (variable, but usually 30 seconds) for all queries, and the SSB RECEIVEs are in a 5-second WAITFOR as well.
    – mwigdahl
    Dec 31, 2012 at 14:29

1 Answer 1

Reset to default
6

You could use a different database for your SSB load. It seems like your SSB data is transitory in nature. That would allow you to either switch to SIMPLE recovery model for that DB or throw away the log backups in case you need FULL for some other reason like mirroring.

I don't think you'll be able to reduce log usage for SSB operations. I'm interested to see other answers but usually log usage is not tunable for OLTP-style transactions. They are always fully logged. Minimal logging only applies to special cases.

Improve this answer
1
  • 1
    That makes sense, and is probably the way we'll end up going if I don't get any supersecret tweaks from other answers... :) Thanks!
    – mwigdahl
    Dec 28, 2012 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.