October 24, 2003 at 1:27 pm
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.
Thanks
-kevkaz
October 24, 2003 at 2:07 pm
Read BOL entry on Checkpoints and the Active Portion of the Log.
If you have the recovery interval set to the default of zero, then (unless you're using the simple recovery model with a small log file), the system will auto checkpoint when the number of tran log records since the last checkpoint reaches a number that the system has calculated could take about one minute to recover.
If you have some large atomic transactions, these could cause infrequent checkpoints (and much longer recovery if one of these transactions must be rolled back) only because a checkpoint cannot be done while open transactions exist; but that doesn't fit your description.
--Jonathan
--Jonathan
October 24, 2003 at 2:24 pm
"checkpoint" interval depands on recovery interval and records of log.
October 24, 2003 at 2:50 pm
I have read BOL numerous times... and all transactions are very short in duration. What I see happening is completely contradictory. I have trace flag 3502 on and the log shows checkpoints happening very infrequently for this database.
I have another production DB that uses the 'simple' recovery model and that one checkpoints much more frequently.
October 24, 2003 at 2:51 pm
Guess I should contact MS, eh?
October 24, 2003 at 3:08 pm
quote:
I have read BOL numerous times... and all transactions are very short in duration. What I see happening is completely contradictory. I have trace flag 3502 on and the log shows checkpoints happening very infrequently for this database.I have another production DB that uses the 'simple' recovery model and that one checkpoints much more frequently.
Change the recovery model of the other database to match that of this database and then size both log files so that they won't get more than 70% full before you back them up. Back them both up so they get truncated and then check their growth during the day. Use DBCC SQLPERF(LOGSPACE) and multiply the log size by the % used. The relative frequency of auto checkpoints should match the growth of the relative used sizes of the logs. If not, there's something anomalous that may indeed warrant a call to PSS.
--Jonathan
--Jonathan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply