Log flushes per second appears low

Question

I've newly taken over administration of a system with about 25 databases. Using Redgate Monitor to chart some metrics, I see that each day, one database shows values for log flushes / second that look like a "normal" chart - a zig-zag line throughout the day.

Redgate explains the metric: "A log flush occurs when a transaction is committed and data is written from the log cache to the physical transaction log file."

All well and good - work is happening and data is getting flushed to disk as it commits.

My question is - why do all the other databases fail to register anything much above the x-axis at all?

Redgate also explains: "Log flushes per second should generally correlate with the number of transactions per second."

When I view transactions per second, plenty of databases are showing zig-zag chart lines - so what's going on that only one of them is showing activity for log flushes? Does this mean all the other databases are failing to commit?

All user databases are using full recovery model.

(I also note between 2am and 5am there are huge spikes in log flushes for almost all databases - still to investigate the cause of these).

Answer 1

I don't know anything about the Redgate tool you're using, but it sounds like "transaction per second" would include read and write transactions together. So the likely explanation is that the one database with high log flushes per second is the only one with a significant write workload (thus generating log flushes). The other databases may have lots of read transactions, but these don't generate any log activity.

Regarding your note at the end, the log activity in the middle of the night is probably due to index maintenance, which is a common thing to do overnight that generates lots of log records.

Without details about your databases, these are mostly just educated guesses based on the information provided in your question.

Answer 2

Log flushes per second doesn't mean anything by itself. It goes up when there are many transactions happening - that's all.

Are users complaining that they are loosing their work after they hit submit button? Are users complaining that application is slow? If not, don't worry abt it. It could be a completely normal pattern for your apps.

When users do complain, see what redgate metrics differ then compared to the norm and then go digging from there.