Tomcat freeze randomly on alfresco 7.3 with MS-SQL db

[Ghanshyam_R]

Team,

We are using alfresco enterprise 7.3 version with GCP hosted MS-SQL database [SQL Server 2019 Enterprise] with 40 core / 256GB RAM with 6 ACS host in clusters and 2 solr hosts in GCP environment.

Since document coming from SAP in high volume, we see alfresco tomcat sometime freezed, that resulted into hanged application (application not responding at all and tomcat frozen, only restart is option that time.)  we see DB CPU utilization didn't even go beyond 50%, but after restart it resume operations, since nodes are in clusters, it is affecting other hosts as well, and we need to restart those things.

Is there any issue with MS-SQL and Alfresco connections? I faced similar thing in past alfresco version around 4.x as well, My-SQL and Postgres works fine with alfresco.

Is there any tuning parameters to "Alfresco" configuration  or  "MS-SQL"  let me know.

Even checking logs doesn't help with Root Cause.

Please advice.

[LeoMattioli]

HI,

I encourage you to open a case on Hyland support portal https://community.hyland.com/ so an Analyst can investigate deeper on your case and also because this portal is a "best effort service". 

About MSSQL, you can  check the Snapshot isolation and Read committed with this query

SELECT name, snapshot_isolation_state, snapshot_isolation_state_desc, is_read_committed_snapshot_on FROM sys.databases where name='<your_alfresco_db>'

You should have 1, ON, 1. If not you can set it with this:

ALTER DATABASE <your_alfresco_db> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

Other suggestion and if this fits your use case, consider to remove 1 ACS node from cluster and use it only for external SAP integration: in that way a big load on that node will not affect the rest of the cluster.

Hope this helps

[Ghanshyam_R]

Thanks Leo Mattioli,

I checked it is set to 1,ON,1 as you said. but can you tell me, what it do and its effect on performance?

[Ghanshyam_R]

Also I see, whenever application and database active connection increased beyond 200, we see tomcat freezing and not responding.

We receive multiple INCIDENTS due to this. Now whenever app is stucked we can't even take thread dumps or JXM because app is freezed or not responding. 

What can be done in this case?

restarting all 6 hosts in cluster helps only to release connections/issues with DB.