Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
    
If the disks are busy during the checkpoint process.... all you can do is wait. Consider upgrading the disk system. –  Jon Seigel Aug 2 '12 at 0:49
    
Thanks Jon. I think the odd thing is that the behavior on this database server seems to have changed recently - but nothing else has. –  apt605 Aug 3 '12 at 1:25
add comment

1 Answer

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).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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