I was working on an implementation of Service Manager and Orchestrator where I was running a significant number of runbooks on a regular basis. This eventually ended up creating logs for over 200,000 runbook executions. What I noticed was that the nightly log purge job was not running correctly. It was failing after several hours and was not successfully clearing the logs. The result of this was extremely long load times in the runbook designer and on the orchestrator web console.
I went to go and purge the logs, but found that the suggestions online were going to take a fair amount of time. This would cause the orchestrator database to be under heavy IO and would negatively affect user experience.
I finally went to truncate the log tables. However, I found that I was unable to truncate the POLICYINSTANCES table, which was the one I most wanted to clear. I tried removing the contraints on the table by altering the table with NO CHECK CONTRAINT ALL, as well as disabling triggers. This was not successful at allowing me to truncate it.
The final solution was the following, which included truncating some of the tables, but running a delete operation against the POLICYINSTANCES table. I hope this helps others who are having this challenge to be able to quickly get their logs in a good position.
Steps to clear Orchestrator logs manually:
- Backup your Orchestrator database
- Validate all Orchestrator services / runbook services are stopped
- Run the following commands from inside SQL Management Studio
DELETE FROM POLICYINSTANCES
TRUNCATE TABLE OBJECTINSTANCEDATA
TRUNCATE TABLE OBJECTINSTANCES
TRUNCATE TABLE EVENTS
Have a great day!