Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Dec 30 '12 at 15:53

This question came from our site for professional and enthusiast programmers.

1  
I assume you backup the log also in a high frequency? –  rene Dec 28 '12 at 16:28
    
Yes, every minute. –  mwigdahl Dec 28 '12 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 Dec 30 '12 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 '12 at 14:29

1 Answer 1

up vote 6 down vote accepted

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.

share|improve this answer
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 '12 at 22:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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