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

Tags