locked
What maintenance should be done on the sql server instance housing Local Service Bus? RRS feed

  • Question

  • The sql instance hosting local service bus has a log file (SBMessageContainer01_log.ldf) that is suddenly consuming 40GB of drive space. It consumed all available drive space.

    Also, reviewing the tables in the SBMessageContainer01 database shows most tables have a reasonable number of entries, but two tables have very high numbers of entries (BodiesTable and MessageReferencesTable).

    What SQL maintenance should be performed to keep the database size down and the log low?

    Monday, July 15, 2013 3:59 PM

All replies

  • here is a link to Service bus on Windows server management on MSDN, look for Best practice analyzer and Disaster recovery

    they are not the most comprehensive list of maintenance items but its a good starting point at least for the Service bus specific list, and in General SQL Dba would have the databases on the regular maintenance mode

    -----------------------------------------

    Please mark post as answered if it helped


    Please mark as answered if it helped Vishal Narayan Saxena http://twitter.com/vishalishere http://www.ogleogle.com/vishal/

    Monday, July 15, 2013 5:48 PM
  • In order to reduce the log file size for SBMessageContainer01_log.ldf, I followed this algorithm:

    a. Backup the database

    b. Set the database to use Simple recovery

    c. Shrink the database

    I'd not recommend Simple as a production setting, but the above is the algorithm used to reduce log size.

    In regards to the two tables mentioned, using Service Bus Explorer, I found a couple entities that had hundreds of thousands of messages queued up or in the dead letter queue. Upon deleting those entities the table row counts were significantly reduced.

    While the best practices analyzer is helpful, it didn't address these issues.

    Tuesday, July 16, 2013 1:16 PM
  • Hi,

    I'm  facing the same issue, the BodiesTable and MessageReferencesTable are growing on a daily basis. This is happening even though there are no messages in any of Service Bus entities, including dead letter queues.

    Any idea of why this is happening? I don't think truncating tables in production is something desirable...

    I'm using Service Bus explorer to check for the existence of any messages.

    Thanks,

    JCS

    Thursday, August 8, 2013 9:37 AM
  • I appreciate this is a rather old post, but wondered if anyone had found anything? My BodiesTable has swelled to a massive size, whilst all the queues/topics are empty.

    Steve G

    Tuesday, July 10, 2018 2:23 PM
  • Hey Steve,

    As you are following up on the old thread (2013), many things have been changed since then. I suggest you create a new thread with more details about your scenario.

    Thursday, July 12, 2018 3:20 AM