SQL Errorlog Constant FlushCache Messages

Question

A server that is part of a SQL 2017 Always on availability group (BAG) I have noticed an increase of these messages. I understand these are messages that appear in the log as standard since 2012 (trace flag prior to 2012) but over the last 2 days they are appearing every few minutes, there are no backups running or maintenance jobs at these times

The server is acting as the secondary non readable fail over partner and the primary server is not exhibiting the same behavior.

09/04/2020 10:52:24,spid82s,Unknown,FlushCache: cleaned up 2303 bufs with 1881 writes in 81090 ms (avoided 11 new dirty bufs) for db 7:0 09/04/2020 10:52:07,spid52s,Unknown,last target outstanding: 2 avgWriteLatency 86 09/04/2020 10:52:07,spid52s,Unknown,average writes per second: 17.70 writes/sec average throughput: 0.19 MB/sec I/O saturation: 13073 context switches 15434

There is also a warning in the system error log about disk issue, seconds after this the availability group failed over ever since the FlushCache messages have increased.

Has anyone experienced anything similar or have any advice, my sysadmin is also looking at the SAN and VMware estate.

Had a sudden thought could these be caused by autogrowth activities?

Answer 1

Switch to indirect checkpoints (generally, set recovery interval time to 60 seconds, at least for user databases).

I wrote about this issue here and here, and you should read those posts in full before making the change, though it’s pretty much a no-brainer IMHO.

The impact in our environment was drastic and immediate, and it was simple to prove the change was responsible. Every database with the symptom went from 30-60 second checkpoints, and errorlogs full of these messages, to sub-second checkpoints and no more errorlog entries.

There are probably mitigation techniques for your workload, too, for example see this post by Itzik Ben-Gan but making checkpoints more efficient is a quicker and easier path. Your storage and VM folks aren’t off the hook, though; there is definitely a disk issue they need to address (but I disagree that these FlushCache messages were the root cause of any failover; more likely they are just a different victim/symptom).

Answer 2

Check with your storage admin if there's any firmware updates recently or your storage subsystem needs a newer firmware update. I've seen this messages on SQL environment multiple times and one thing you can look at is the storage layer configuration (this is the common cause). Also, one thing to monitor your disk performance using perfmon. disk counters you need to focus on: avg disk sec/Read, avg disk sec/Write, avg disk sec/Transfer.

You can check the following link you can look below:

1. How It Works: When is the FlushCache message added to SQL Server Error Log?
2. https://techcommunity.microsoft.com/t5/sql-server-support/how-it-works-when-is-the-flushcache-message-added-to-sql-server/ba-p/317038

When I say storage layer, check the following: HBA/CNA, Fibre Cable, FC Switch, Ports, Storage controller, Software (MPIO drivers) - all of its configuration

Finally, I'd high recommend to always check and investigate warnings/errors message on your event logs. DO NOT let it pass because this kind of messages have long term consequences down the road.