IAM_SF system flow logs are flooding the SQL server 2021.3

Hi,

In the 2021.3, all Creation steps run via System flows, which results in many Log items. This should not affect performance of Indicium in any way. We do have a story on our backlog to automatically delete these created log items and will most likely be implemented in the next version → 2022.1.

What you can try is restarting Indicium. This will certainly decrease the CPU usage, if not - I'm not sure your server/machine is capable enough of running all applications in IAM (including the Software factory). 

Which version of Indicium are you running on?

Hi Mark,

Indicium is not affected, i have upgraded everything to the latest versions last week (15th). The SQL database IAM_SF is affected by this en causing troubles. (high CPU usage, performance warnings in Azure, 600K records in a week added, queries running for 3, up to 6 hours and increasing by the day).

Ps. Indicium is restarted.

I think that was it! I downloaded the install packages on eary morning the 15th and missed this hotfix. After installing it, the loads are minimal again:

Great! Enjoy the newest version 

Still it would be nice if we can disable such kind of logging. It still costs more resources then on 2021.2. Therefor we had to increase the Azure pricing tier (and increases the montly costs).

I can understand that, feel free to create an Idea for this here on the Community. Our Indicium team could look into this more thoroughly and see which type of logging could be made optional.

@Mark Jongeling for example; on our previous settins our complete application gets unresponsive on Universal and kicks out every user:

Error Number:10928,State:1,Class:20
ClientConnectionId before routing:0c8edfba-efb5-489a-8619-2604c6989b63
Routing Destination:c36d32a0b258.HS1.tr9921.westeurope1-a.worker.database.windows.net,11005
2021-11-22T12:55:39.0596633+00:00  [ERR] Error scheduling system flow 'system_flow_generate_definition' for application 4. (c6d61a31)
Microsoft.Data.SqlClient.SqlException (0x80131904): Resource ID : 1. The request limit for the database is 60 and has been reached. See 'http://go.microsoft.com/fwlink/?LinkId=267637' for assistance.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
   at Microsoft.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, Boolean isInternal, String endMethod)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at Microsoft.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location ---
   at Indicium.BackgroundServices.SystemFlowSchedulerBase.RunSystemFlow(Int32 guiApplID, TSFApplication application, FullProcessFlow systemFlow, DateTime scheduledTime) in C:\azp\agent\_work\1\s\src\Indicium\BackgroundServices\SystemFlowSchedulerBase.cs:line 112
   at Indicium.BackgroundServices.SystemFlowScheduler.RunSystemFlow(Int32 guiApplID, TSFApplication application, FullProcessFlow systemFlow, DateTime scheduledTime) in C:\azp\agent\_work\1\s\src\Indicium\BackgroundServices\SystemFlowScheduler.cs:line 90
   at Indicium.BackgroundServices.SystemFlowSchedulerBase.ScheduleSystemFlow(Int32 guiApplID, String systemFlowID, DateTime scheduledTime) in C:\azp\agent\_work\1\s\src\Indicium\BackgroundServices\SystemFlowSchedulerBase.cs:line 84

This problem is occuring again. We need to delete the log files manually from the tabel once every few days or the CPU usage will rise 10% every day. The query that is fired and causing this issue is: 

Hi Arno,

For the next version of IAM and Software Factory we are planning on emptying logging tables such as the process flow schedule log. We plan to delete any logging records older than 7 days, which will be checked and then deleted every day. 

Would that suffice? For now it does require a manually query to empty the log, sorry for the inconvenience.

Thanks for the fast reply Mark, appreciated. These log files don’t contain any valued information for us. If we can change the no of days, we can set it to 1 (for example) and we can downscale our Azure pricing tier again. 7 days will be even to much for our current setup.

We'll take that into account