ControlPoint 5.x

Situation

I have deleted about 50+ policies in ControlPoint and am running the Cleanup Policies scheduled task.
The Cleanup Policies.log shows the following timeout error:

Error Timer - Exception happened while executing command: System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. --->

How can I overcome this issue?

Resolution

There are 2 ways to reduce the likelihood of experiencing a timeout:

1) Reduce the number of policies currently marked for delete.

You can do this by setting MarkedForDelete=0 (do not delete) for some of policies that were previously deleted via the CP UI.

First lookup the PolicyId for the policies via:

SELECT * FROM [ControlPoint].[dbo].[CPPolicyVersion]

Then use this PolicyId in place of Id in the SQL below:

Update [ControlPoint].[dbo].[CPPolicy]
set MarkedForDelete=0
where Id =? and MarkedForDelete=1

When you refresh the view of policies in ControlPoint the policy will no longer show Deleted in red.

This will help reduce the backlog of deleted policies Cleanup Policies has to process at any one time so the timeout is less likely to occur.
Then run Cleanup Policies and review the Cleanup Policies.log to check it was successful.

You can always mark any policy for delete again in the future using the ControlPoint UI by selecting the policy and clicking Delete.

2) Increase the timeout used by Cleanup Policies.

During a quiet period where users are not using the system and there are no background activities ongoing increase the following timeout in the database:

SELECT * FROM [ControlPoint].[dbo].[CPGlobalSettings]
where settingname like 'Autonomy.ControlPoint.SqlCommandTimeout'

The default value is 1500 seconds but take careful note of what value you are currently using in case it differs in your environment as you will need to reset this back after Cleanup Policies finishes.

Increase this timeout to a much larger value e.g. 30000 seconds.

update [ControlPoint].[dbo].[CPGlobalSettings]
set settingvalue=30000
where settingname like 'Autonomy.ControlPoint.SqlCommandTimeout'

Restart CP Engine and run Policy Cleanup task again.
This will increase the amount of time the task can run for before it reports a timeout.
Review Cleanup Policies.log to ensure it runs successfully.
If it is still reporting a timeout you can increase the timeout further, restart the CP Engine and run it over a long weekend.

NOTE: It is critical to reset Autonomy.ControlPoint.SqlCommandTimeout back to its former value after Cleanup Policies finishes because this same timeout setting is used by many other processes in ControlPoint. Leaving this with a very high value would have adverse effects and could lead to long running transactions and contention in the database.

So after you have successfully removed all the policies you wish to delete you can reset it back to its default.

update [ControlPoint].[dbo].[CPGlobalSettings] set settingvalue=1500 where settingname like 'Autonomy.ControlPoint.SqlCommandTimeout'

Access support article here