Blocked Sessions after Upgrading to SQL Server 2017

[maryjane_steele2]

After upgrading to SQL Server 2017 I am experiencing Blocked Sessions on NRIV and ARFCSSTATE. SAP says to buffer NRIV and to disable page level locking on ARFCSSTATE. I have also had a couple of blocked sessions on my BW system on a BPC table. The curious thing is that in the 12 years I've worked here I have never had issues with Blocked Sessions until I upgraded to SQL Server 2017. Just curious if anyone has had similar issues.

[mamartins]

Did you adjusted the MSSQL flags on the startup parameters according to the new MSSQL version?

This new version changed the behavior of some flags and made other obsoletes. Plase check the SAP note https://launchpad.support.sap.com/#/notes/2484674

[patelyogesh]

Hello

I would suggest you to start looking from SAP notes below

• 2469364 - Locks in table NRIV
• 375566 - Extremely large number of entries in tRFC and qRFC tables

Please let me know if this still not resolve this issue.

Thank you

Yogesh

[maryjane_steele2]

Well my ERP system is the main system having this issue, it only happens sporadically on BW or CRM. Yes I have checked PAM and note 2492596. Here is the entry in lock history, followed by the sql statement causing issues.

2019-10-31 07:16:01.067 337 452 9332 1 suspended erp #### R3D41 comm rd ODBC SELECT SELECT TOP 1 "CLIENT" ,"OBJECT" ,"SUBOBJ 271,794 KEY: 5:72057645576224768 (9b2188ee93b2) LCK_M_U ERP NRIV (9b2188ee93b2) KEY U WAIT 1 TRANSACTION

SELECT TOP 1 "CLIENT" , "OBJECT" , "SUBOBJECT" , "NRRANGENR" , "TOYEAR" , FROMNUMBER" , "TONUMBER" , "NRLEVEL" , "EXTERNIND" FROM "NRIV" "NRIV" WITH ( UPDLOCK , REPEATABLEREAD ) WHERE "CLIENT" = @P1 AND "OBJECT" = @P2 AND "SUBOBJECT" =@P3 AND "NRRANGENR" = @P4 AND "TOYEAR" = @P5 /* R3:SAPLSNR3:1543 T:NRIV M:300 */

[S_Sriram]

Hi Mary.

1. You SAP BI system supported MS sql 2017? Have you check the SAP PAM? refer the SAP note 2492596 about the SAP Version supported MS Sql 2017.

2. Could you share the error in detail about blocked sessions?

Regards

Sriram