Question Re: SQL Anywhere starting checkpoint in Technology Q&A

SQL Anywhere starting checkpoint

former_SQLA_member1694909 — Thu, 04 Feb 2021 06:14:16 GMT

SQL Anywhere 12.0.1.3797

In the logfile of dbsrv12 I see "Starting checkpoint of...". and "Finished checkpoint of ...". This is normally very fast for 1 database. This engine manages 240 databases and it takes serveral minutes. The processor peeks to 100% and we can not log in anymore.

Is there anything we can do to prevent starting checkpoint of all the databases at the same time?

Re: SQL Anywhere starting checkpoint

Breck_Carter — Thu, 04 Feb 2021 08:01:53 GMT

Does this happen every time a checkpoint is taken for one database, they are taken for all 240?

Or does it just happen when dbsrv12.exe is started and stopped? (which is expected)

Re: SQL Anywhere starting checkpoint

former_SQLA_member1694909 — Mon, 08 Feb 2021 03:41:31 GMT

No, this does not happen every time. I just noticed that dbsrv12 sometimes (1 or 2 times in a week) takes 100% processor time for a short period (few minutes). In the log file I see a lot of Starting and finished checkpoints. Just before these checkpoints I see a lot of

Disconnected TCPIP client's AppInfo: Connection terminated abnormally

When is a start checkpoint logged?

Re: SQL Anywhere starting checkpoint

VolkerBarth — Mon, 08 Feb 2021 05:37:22 GMT

The server decides when to do checkpoint (among other conditions) based on the "idle time", so could it be the server has busily served many connections (and thereby needed to delay checkpoining) and now, those connections somehow unexectedly were disconnected (so rollbacks must be applied), and after that, it is now "idle" enough to do those checkpoints?

Do you know why so many connections where abnormally terminated - because that woulds conern me more...?

When is a start checkpoint logged?

What do you mean by that? By default, the start is logged in the message log, so I don't quite understand your question.

Re: SQL Anywhere starting checkpoint

Breck_Carter — Mon, 08 Feb 2021 07:39:03 GMT

Correlation is not causation... it is very unlikely that the checkpoints are the cause of any performance problem; they may be a symptom of some other problem, but not the cause.

FWIW Foxhound works with Version 12 target databases, and it may provide more information about what is going on. (Note that Foxhound itself runs on its own instance of the 32-bit or 64-bit SQL Anywhere 17 network server build 4882 or later; see the Requirements.)