Does anyone have a good understanding of the algorithm used by Sqlsrv to determine when a checkpoint should be taken? I have a DB that takes on quite a bit of DML (hundreds per minute), but is CHECKPOINTed once an hour, at best... sometimes as long as 3-4 hours in off-peak times. The only thing I can think of is that the trans log is quite large (for reasons I won't go into now) and SS figures that a tiny percentage of the log is full, so it does not checkpoint... that doesn't make much sense to me though.
It seems kind of odd that the "recovery interval" setting is the duration that you're willing to wait for all transactions to roll forward - I'm not all that confident in specifying any value if right now SS figures that its okay to let tens of thousands of dirty pages exist before checkpointing...
Anyone else see this & what have you done? I guess I could set up a job to manually checkpoint every 15 minutes or so... but don't feel that I should need to do that.