SAP ASE (sybase) historical monitoring tables

Adaptive Server maintains context information for each client connection that accesses the historical tables, and on each successive query on the table returns only rows that the client has not previously received (read here).

The historical monitoring tables are:

• monErrorLog
• monDeadLock
• monSysStatement
• note: When executing a procedure from a procedure, monSysStatement return a record both to the two procedures.
• monSysSQLText
• monSysPlanText

The maximum number of statement statistics returned can be tuned with statement pipe max messages.

The historical monitoring tables require the enable monitoring, statement statistics active, per object statistics active, statement pipe max messages, and statement pipe active configuration parameters to be enabled.

Each message stored adds one row to the monitoring table. Once all entries in the buffer have been used, new messages overwrite old messages in the buffers, so only the most recent messages are returned.

Filter the result set with a where clause filters from the "statement pipe max messages" number.

select SPID, ErrorMessage from master..monErrorLog

SPID      ErrorMessage

------    --------------------------------------

20        An error from SPID 20

21        An error from SPID 21

(2 rows affected)

 

-- ==========================

-- in a different session:

-- the filter return only one rows

select SPID, ErrorMessage from master..monErrorLog

where SPID=20

SPID ErrorMessage

------ --------------------------------------

20 An error from SPID 20

(1 row affected)

 

--And:

 

-- but the filtered rows was set as "returned" in the previous query and won't be return without the filter now:

select SPID, ErrorMessage from master..monErrorLog

where SPID=21

SPID ErrorMessage

------ --------------------------------------

(0 rows affected)