Nservicebus 6 Database Log Full

1

We have encountered this issue on our production database wherein the the Host tables was hosted. We would like to understand on why the services creates a lot of queries causing our database log file to be full and what will be the possible fix. Thanks!!

NserviceBus: Version 6.0
Transport: SQLServer Transport

2

Hi @raymondpunzalan10,

SQL Transport is using your database as a queue. Although it includes some backoff logic, that requires pretty much constant querying to see if the queue (table) has any new messages to process.

The queries that are used are documented in the SQL Server transport SQL statements article in our docs. Are these what you’re seeing, or something else?

3

On top of what @DavidBoike suggested, you might want to verify a couple more things:

  • Is the outbox enabled on those endpoints resulting in excessive queries?
  • Are there long-running database transactions?

The two things together could be causing the T-Log to grow. Also, keep in mind that T-Loog doesn’t shrink, once the database server acquired the disk space it’s not released to the OS unless a log shrink happens.

4

Hi @DavidBoike @mauroservienti - yes that’s the query we are seeing. any fix we need to do to prevent the T-Log to grow because of this queries?

5

@raymondpunzalan10,

which query are you referring to? David linked to an article with a few queries.
Can you also, please, answer my questions above?

Regards,
.m

6

@mauroservienti hmm… not that familiar with the outbox… can you tell me more about it?
our service is just doing normal sql queries like select/insert/update/delete so not that long running queries.

7

The outbox is an NServiceBus feature that can be enabled at the endpoint level. More information on the documentation.

When we say long-running, we’re not talking about minutes or more.

  • When a transaction is committed, it goes to the T-Log on disk (increase the size) and it is also written to pages in memory.
  • At some point, the database server will sync pages to disk and cut off the transaction log. However, disk space is not released.
  • Large transactions will have proportional entries in the transaction log.
  • On top of that if there are long-running transactions the log cannot be cut-off until those are completed. And that adds up.

The outbox and the outbox clean-up process could concur with that, and the message size impacts the transaction size.

Would it be an option for you to provide the results of this query Script: open transactions with text and plans - Paul S. Randal that lists pending transactions? (you can safely remove lines 30 and 31, there is no need for the query plan)

Also, one clarification. Is the T-Log growing continuously or is it just bigger than expected?

8

Will the outbox help resolve the T-Log growing continuously?

Seems that link for this is not working already Script: open transactions with text and plans - Paul S. Randal