Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

The following evidence indicates that a checkpoint in SQL Anywhere Network Server Version will block a scheduled event from firing... either that, or it blocks the INSERT performed as the first statement inside that event.

Here are some snippets of code...

  • First, the debug_watch table used to record the event,
  • Second, the CREATE EVENT and INSERT statements,
  • Third, the rows in debug_watch showing the sudden five-second delay between inserts, and
  • Finally, the output from sa_server_messages() showing that the delay encompassed the Starting/Finished checkpoint timestamps.
CREATE TABLE debug_watch (

CREATE EVENT rroad_watch_server_messages
INSERT debug_watch ( pkey ) VALUES ( DEFAULT );

-- debug_watch...
1556,'2011-01-23 11:12:30.020'
1557,'2011-01-23 11:12:31.004' -- one second later
1558,'2011-01-23 11:12:32.098' -- one second later
1559,'2011-01-23 11:12:37.754' -- FIVE SECONDS LATER
1560,'2011-01-23 11:12:39.051'
1561,'2011-01-23 11:12:40.035'

-- sa_server_messages()...
323,'Starting checkpoint of "f" (foxhound1.db) at Sun Jan 23 2011 11:12','2011-01-23 11:12:32.707','INFO','CHKPT','f'
324,'Finished checkpoint of "f" (foxhound1.db) at Sun Jan 23 2011 11:12','2011-01-23 11:12:37.739','INFO','CHKPT','f'

asked 23 Jan '11, 16:28

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

Short answer: Yes.

Here is why:

When an event (or any connection) needs to run its sequence of statements, it must first be assigned to a server worker (aka a thread in the server) and that worker needs to acquire a connection to the database. By this I mean that the worker must be granted access to the database so that it can read and update the database data.

During the critical point of a checkpoint - the time in which the server is taking a consistent snapshot of the database and writing that snapshot data to disk - all workers are forbidden access to the database so that they do not make any changes to the in-memory contents while the snapshot is being taken.

So during the time that the checkpoint snapshot is being taken on a busy database - one with lots of on-going updates - the checkpoint can take several seconds (exact amount of time will depend on the number of recent updates and the speed of your I/O system) and all workers, including events, will momentarily stop what they are doing and wait until the checkpoint snapshot is complete.

In your particular example the checkpoint appears to have taken a little over five seconds and during that time you event will not have been allowed to run.

permanent link

answered 23 Jan '11, 18:08

Mark%20Culp's gravatar image

Mark Culp
accept rate: 41%

Comment Text Removed

There are periods during any checkpoint in which no other operations are permitted; however, the durations of those periods are not predictable. Idle checkpoints are also different from explicit checkpoints since idle checkpoints allow new modifications to the database while all of the dirty pages are written out of the cache but explicit checkpoints do not. Idle checkpoints is actually a bad name and doesn't really mean that the server is idle -- they are the checkpoints that occur due to checkpoint urgency and max recovery time settings.

There can also be a lot of delays associated with a checkpoint before the "Starting checkpoint" ever shows up. See However, it would seem that you are interested in delays occurring between the Starting & Finishing messages. Do you know if the long checkpoints you are having problems with are idle checkpoints or explicit ones? If they are idle, is the server unresponsive the whole time? If they are explicit, does the duration of the checkpoint approximately match the time it would take to write out the expected number of dirty pages in the cache?


permanent link

answered 23 Jan '11, 18:07

John%20Smirnios's gravatar image

John Smirnios
accept rate: 37%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 23 Jan '11, 16:28

question was seen: 2,411 times

last updated: 23 Jan '11, 18:08