The Oscar Procedure 'dbo.ProcedureName' reached its batch duration limit with additional records remaining to be removed.

Question: I am getting these Oscar alerts, how do I re-mediate this?

Answer: If, during our nightly database cleanup, the cleanup is not finished because of excessive records, an email will be sent to master admins.

Resolution: Execute the procedure manually by following these steps: 

1. To check the batch size use the below query - this will show the cleanup setup for this procedure and also show the last run count, i.e. procedure 'dbCleanupEventInstanceHistEventOrphans':

SELECT * FROM dbo.dbCleanupProcSettings
WHERE ProcName LIKE '%dbCleanupEventInstanceHistEventOrphans%'

2. To find out how many pending records need to be cleared, use the query below - this has to be customized based on the alert you are getting: 

SELECT COUNT(*) T1
FROM hermes.EventInstanceHistory T1
LEFT JOIN hermes.EventInstance T2 ON T2.EventID = T1.EventID
WHERE T2.EventID IS NULL

Example:

SELECT COUNT(*) T1
FROM wslogdetail T1
WHERE NOT EXISTS (SELECT 1 FROM wslogBase WHERE TransactionId = T1.TransactionId)

3. Count the pending rows to be deleted by each cleanup routine and execute the procedure manually:

EXEC dbCleanupEventInstanceHistEventOrphans
@Batchsize = 1000​

Note: Set a batch size based on the number of records to be cleaned up and once the rows are cleaned up, you should no longer get these emails. Now, if this query takes more than 10 seconds to execute, please reduce the batch size and run this in multiple intervals.

Was this article helpful?
6 out of 8 found this helpful
Have more questions? Contact us