All we need is an easy explanation of the problem, so here it is.
Using SQL server 2017 (14.0.3381.3) with Always On High Availability environment.
Getting error:"Buffer Pool scan took 10 seconds: database ID 8, command ‘CHECKPOINT’, operation ‘FlushCache’, scanned buffers 144321190, total iterated buffers 150079215, wait time 42 ms. See ‘https://go.microsoft.com/fwlink/?linkid=2132602’ for more information"
This is a new feature that is included in the 2017 CU23, under the VSTS bug number 13741858.
I see the list of all the operations that can trigger a scan but none of them are happening on my machine, is there a way to tell what causes the scan? – OR – Is there a Trace Flag that can be used to turn off the log of this error?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
I see the list of all the operations that can trigger a scan but none of them are happening on my machine, is there a way to tell what causes the scan?
It’s a CHECKPOINT on database ID 8. It says so right in the message.
Do you have INDIRECT CHECKPOINT enabled? What does
select database_id , name, target_recovery_time_in_seconds from sys.databases where database_id = 8
return? The default of 60 enables indirect checkpoints.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂