Why are there gaps in sa_server_messages()?

[Breck_Carter]

How does the SQL Anywhere engine decide which old sa_server_messages() to purge?

The actual behavior (for 16.0.0.1915) seems a bit odd: the first 40 messages are preserved, but after that random chunks are deleted.

Here is a snapshot for one server instance started on March 13...

SELECT * FROM sa_server_messages();

msg_id,msg_text,msg_time,msg_severity,msg_category,msg_database

0,SQL...Version 16.0.0.1915\\x0d\
,'2019-03-13 00:46:09.026','INFO','STARTUP/SHUTDOWN',
1,Workgroup Edition,                '2019-03-13 00:46:09.135','INFO','STARTUP/SHUTDOWN',

   ... rows exist but are omitted

39,TCPIP link started successfully,'2019-03-13 00:46:25.600','INFO','CONN',
40,Now accepting requests,         '2019-03-13 00:46:25.631','INFO','STARTUP/SHUTDOWN',

   ***** ROWS MISSING *****

2473,Disconnecting shared memory client, process ...,'2019-03-27 16:25:23.414','INFO','CONN',
2474,Disconnected SharedMemory client's AppInfo: ...,'2019-03-27 16:25:23.460','INFO','CONN',

   ***** ROWS MISSING *****

2596,DIAG 2019-03-28 10:26:15.481 @ftp_upload_at = ...,'2019-03-28 10:26:15.481','INFO','MSG','ruralfinds16'
2597,DIAG 2019-03-28 10:26:17.318 prepare_ftp_uploa...,'2019-03-28 10:26:17.318','INFO','MSG','ruralfinds16'

   ***** ROWS MISSING *****

2792,Hello,'2019-03-29 14:06:56.716','INFO','MSG','ruralfinds16'
2793,Hello,'2019-03-29 14:07:02.074','INFO','MSG','ruralfinds16'

   ... rows exist but are omitted

2813,Disconnected SharedMemory client's AppInfo: ...,'2019-03-29 15:55:35.236','INFO','CONN',

   ***** ROWS MISSING *****

3098,Disconnecting shared memory client, process ...,'2019-03-31 09:50:58.238','INFO','CONN',
3099,Disconnected SharedMemory client's AppInfo: ...,'2019-03-31 09:50:58.254','INFO','CONN',

   ***** ROWS MISSING *****

4032,Starting checkpoint of "ruralfinds16" (rural...,'2019-04-05 23:42:52.170','INFO','CHKPT','ruralfinds16'
4033,Finished checkpoint of "ruralfinds16" (rural...,'2019-04-05 23:42:52.342','INFO','CHKPT','ruralfinds16'

[VolkerBarth]

Does that quote from the docs answer your question?

When new messages are sent to the console, old messages with the same category or severity are deleted if the number of messages exceeds the value of the MessageCategoryLimit property. As a result, there may be gaps in the result set, and two consecutive rows may not have consecutive message IDs.

FWIW, the default value for MessageCategoryLimit is 400.

[MarkCulp · Accepted solution]

IIRC the server will keep all of the console messages up until it reaches a predetermine limit. There are two limits: (1) a maximum number of messages overall, and (2) a maximum number of messages of each "class" (e.g. INFO, ERROR, etc + connection, checkpoint, etc). If one or both of the limits are exceeded when a new message is generated then the oldest message (overall and/or of the "class") is deleted and then the new message is added.

The idea is to hopefully keep the messages that would be most useful (i.e. most recent of each class) by not allowing a particular class of messages (e.g. checkpoints) from purging other classes of messages that could be of interest.

HTH