3

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?

1 Answer 1

2

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.

4
  • I see, so currently my target_recovery_time (trt) is set to 0, and because of that the CHECKPOINT is automatically triggered. So what are my options? currently i get an error logged approximately every 3 min or so. If I set my (trt) to 60 seconds, would that give me a bigger window, because currently (number of log records * fixed time interval to recover a log record) > 0 (trt). I guess anything more than 0 would probably be better.
    – Tomasz
    Mar 25, 2021 at 12:33
  • Setting the database target_recovery_time to a non-zero value switches to indirect checkpoint, which avoids the full buffer pool scan on checkpoint. But don't set the target_recovery_time too low, as that can cause performance problems. 60 seconds is the default, and is a reasonable balance of performance impact and recovery time. Mar 25, 2021 at 12:38
  • Can this be set during business hours or do i need to wait till after hours to set the target_recovery_time. ALTER DATABASE MyDb SET TARGET_RECOVERY_TIME = 60 SECONDS;
    – Tomasz
    Mar 25, 2021 at 13:20
  • 1
    ran the update after hours just in case, took 20 seconds but the errors stopped! now will monitor the CPU to see if there is any stress. Thank you for your time and your answer David.
    – Tomasz
    Mar 26, 2021 at 2:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.