• Great article as I've found SB to be one of the most underused and misunderstood SQL Server features (along with Resource Governor) and there's a distinct lack of info on troubleshooting SB issues.

    Just to echo Ten Centuries post and point 4 on the conversation timeout, with my last client we found an issue where the application was using SB and the conversations weren't being timed out (it wasn't set, so went to default, which is a billion seconds! :w00t:) The result of this was Service Broker bloat, we could see our main production app db growing by 6GB a day and yet when we were performing a weekly reindex on a 600GB+ db it was completing in under 20 mins. Initially I did think damn those SSDs are good! 😀 Be then we found it was SB causing the issue.

    Couldn't change the app code so we had to perform a staggered CONVERSATION CLOSE as if you do it too harshly it can cause issues.

    more info here[/url]:

    qh

    [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]