6

We seem to have a problem that came out of nowhere. The checkpoint process is stuck in SLEEP_BPOOL_FLUSH state, and not actually reducing the usage in the log file.

This never seemed to happen before.

I understand that SLEEP_BPOOL_FLUSH is a normal wait for the CHECKPOINT process when the disk subsystem is busy. Actually, we've always had a very busy disk subsystem so I don't understand why this has become an issue.

(If you manually run a checkpoint command, same results...your user process waits in SLEEP_BPOOL_FLUSH).

Is there ANYTHING we can do to push the checkpoint to occur? Can the parameters for the checkpoint be tweaked so this does not occur?

(SQL Server 2005, RAID subsystem, Simple Recovery Model)

apt605
  • 83
  • 1
  • 1
  • 5

2 Answers2

4

If Resource Monitor says that SQL Server is actually performing the checkpoint (i.e., it's working okay, just that it's slow), all you can and should do is wait.

If the disk subsystem is busy anyway (shared storage?), is it possible the other load has increased and now the I/O subsystem is near or at capacity? If you aren't measuring (recording) metrics that would tell you so, I would highly recommend this, using either PerfMon directly or some other 3rd-party tool. (It also depends on the storage architecture. The main point is to be able to track the I/O load over time.) If you're on a SAN, was there a change in the I/O subsystem itself that would be transparent to your SQL box?

One thing you can consider is changing the Recovery Interval setting, which lets you manually control how frequently checkpoints occur. Note that this is an instance setting. I strongly recommend reading the documentation thoroughly before changing it, because it's possible you could make things worse by moving away from the default (depending on your workload).

Jon Seigel
  • 16,922
  • 6
  • 45
  • 85
1

While restoring the database from full and differential, I have a wait type - SLEEP_BPOOL_FLUSH and restore stuck after full restore.

Then I issued DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR); to clear the waits.

It was successful, and I was able to restore a differential backup.

user353gre3
  • 1,447
  • 1
  • 13
  • 20
user41182
  • 11
  • 1