Summary
Error
A very large number of records are inserted in the EVENT_LOG table in the site admin db/schema
This can fill the database transaction log, cause errors in the SA logs due to queries timing out.
In some cases millions of records may be inserted into the EVENT_LOG table
Cause
The cause are non-concluded test runs from timeslots and reservations within the lab project.
This typically affects ALM which integrates with Performance Center Server (PCS)
Fix
The non-concluded run reservations can be exposed by issuing the following query to the lab project db/schema
SELECT LRN_ID, LRN_PROJECT_PUID, LRN_RUN_ID, LRN_RESERVATION_ID, LRN_STATE FROM LAB_RUNS JOIN LAB_RESERVATIONS ON
LRN_RESERVATION_ID = RSV_ID WHERE (LRN_STATE LIKE '%Stopping%' AND LRN_ID = RSV_CURRENT_LABRUN_ID AND RSV_DEV_STATUS = 1) OR ('2020-10-06 00:00:00.00' >
DATEADD( MINUTE , isnull((RSV_DELAY), (0)), DATEADD( MINUTE , /*P*/1440, RSV_CREATION_DATE)) AND LRN_STATE LIKE '%Pending%');
To address the issue do the following...
1) Perfom a full backup of the site admin schemadb/schema and the lab project db/schema in Oracle or MSSQL
2) Issue the following query to the lab project db/schema...
DELETE FROM td.LAB_RUNS FROM td.LAB_RUNS AS spqh INNER JOIN td.LAB_RESERVATIONS AS sp ON spqh.LRN_ID = sp.RSV_CURRENT_LABRUN_ID WHERE (LRN_STATE LIKE '%Stopping%' AND LRN_ID = RSV_CURRENT_LABRUN_ID AND RSV_DEV_STATUS = 1) OR ('2020-10-06 00:00:00.00' >
DATEADD( MINUTE , isnull((RSV_DELAY), (0)), DATEADD( MINUTE , 1440, RSV_CREATION_DATE)) AND LRN_STATE LIKE '%Pending%');
3) Issue the following to the site admin db/schema...
truncate table EVENT_LOG;
Note: the above query deletes the records in the EVENT_LOG table without recording to the database transaction log. This is important as a "delete from" achieves the same result but risks filling the transaction log
4) Monitor the EVENT_LOG table in the site admin db/schema. The level of records inserted into the table should be minimal