Watch Out for Zombies When Using Microsoft Dynamics NAV with “xact abort” Enabled


If you have turned on the xact abort connection setting option for the Microsoft Dynamics NAV SQL Server instance, then you should watch out for Zombies. With this option turned on, users of your solution risk seeing an error stating This SqlTransaction has completed; it is no longer usable along with a crash signature which includes a call to the ZombieCheck() function.

You can set the xact abort configuration option on a SQL Server instance by right clicking on the instance name in SQL Server Management Studio and then selecting Properties. Select the Connections page, and then, in the Default Connection Options list as shown in the screenshot below, you will see the xact abort option at the end of the list: 

If you enable xact abort as a default connection option in SQL Server, the SQL Server engine will automatically roll back the current transaction entirely if a Transact-SQL statement executed by the relevant SQL connection raises a run-time error. If this option is turned off then some SQL Server errors may not force a complete rollback (depending on the severity of the error). The above setting defines the default for all connections, but this default can be overridden at connection time on a per-connection basis if the application developer has included code to explicitly set this option one way or the other.

For Microsoft Dynamics NAV 2013 R2 and Microsoft Dynamics NAV 2015, we do not specify any setting for “xact abort” when a connection to SQL Server is being established and therefore pick up whatever default specified at the SQL Server instance level (as shown above). Most of the time this is fine, but in some circumstances we have reports that the  Microsoft Dynamics NAV client may crash with the error message This SqlTransaction has completed; it is no longer usable. If you check in the Windows Event Log on the  Microsoft Dynamics NAV middle-tier machine you will see the following message and stacktrace posted by Microsoft Dynamics NAV Server:

Type: System.InvalidOperationException

Message: This SqlTransaction has completed; it is no longer usable.

StackTrace:

     at System.Data.SqlClient.SqlTransaction.ZombieCheck()

     at System.Data.SqlClient.SqlTransaction.Commit()

     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ExecuteAction(Action action, NavSqlCommand command, Boolean isRollbackAction)

     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ExecuteActionWithTrace(EventTask task, String tenantId, Int32 sessionId, String userName, Action action, NavSqlCommand command)

     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.EndTransaction(NavSqlEndTransactionType endType)

     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.CheckBeginTransaction(TransactionType transactionType)

     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnectionScope..ctor(NavDatabase database, Boolean requireNewConnection, TransactionType transactionType, NavSqlConnection lastUsedSqlConnection)

     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnectionScope.Create(NavDatabase database, TransactionType transactionType, IReference`1& lastUsedSqlConnection)

     at Microsoft.Dynamics.Nav.Runtime.SqlTableDataProvider.BeginTransaction(TransactionType transactionType)

     at Microsoft.Dynamics.Nav.Runtime.TransactionManager.EnsureReadTransactionStarted()

     at Microsoft.Dynamics.Nav.Runtime.TransactionalDataCache.TryGetExists(ExistsCacheRequest request, Boolean& exists, DataLockState& lockState, Int32& transactionBumperToken, Action`1& updateCache)

     at Microsoft.Dynamics.Nav.Runtime.DataAccess.Exists(ExistsCacheRequest request)

     at Microsoft.Dynamics.Nav.Runtime.RecordImplementation.ValidateNonFlowField(NCLMetaField field, Boolean isUserInput)

     at Microsoft.Dynamics.Nav.Runtime.NavRecord.ValidateField(Action`1 fieldEvent, NCLMetaField metaField, Boolean isUserInput)

     at Microsoft.Dynamics.Nav.Runtime.NavRecord.Validate(NCLMetaField metaField, NavValue newValue, NavRecord callerRecord, Boolean isUserInput)

     at Microsoft.Dynamics.Nav.Runtime.NavRecord.ValidateFields(IEnumerable`1 fieldNumbers, NavRecord callerRecord)

     at Microsoft.Dynamics.Nav.Runtime.NavForm.NewRecord(Boolean belowXRec)

Source: System.Data

HResult: -2146233079

 

The scenarios where the above error can happen vary somewhat but the factor they have in common is that the xact abort connection setting is enabled for the relevant SQL Server instance. In future versions of Microsoft Dynamics NAV, we will add code to explicitly disable xact abort for all  Microsoft Dynamics NAV sessions at connection time. However, for Microsoft Dynamics NAV 2013 R2 and Microsoft Dynamics NAV 2015, it is advisable to turn off the xact abort option at the SQL instance level if none of the other databases on the same SQL Server instance require it.

If other application databases on the same SQLServer instance require that the default connection option for xact abort is turned on, and you encounter the above issue with Microsoft Dynamics NAV, then you could consider moving the Microsoft Dynamics NAV database to another instance of SQL Server.

 

Gerard Conroy

Microsoft Dynamics NAV Support EMEA

Comments (2)

  1. Alan says:

    We are seeing the Zombie error with NAV 2013 (cumulative update 22 and prior).

    "xact abort" is NOT enabled on MS SQL 2012 standard.

  2. René Gubler says:

    Hi Gerard

    I checkt this issue on some Servers. Yes, we hav it. Do you fix that in a CU?

    Best Regards

    René

Skip to main content