Microsoft Dynamics NAV - "xact abort" Enabled Settings May Want You to Watch Out for Zombies

This is for all those programmers, developers and users – if in case you have been wise enough, read by oversight" to turn on the xact abort connection setting option for your Microsoft Dyamics NAV SQL server instance – please immediately look out for Zombies.

This is for all those programmers, developers and users – if in case you have been wise enough, read by oversight" to turn on the xact abort connection setting option for your Microsoft Dyamics NAV SQL server instance – please immediately look out for Zombies. If this option is turned on – users of your solution are at a risk of seeing an error thrown at them indicating "This SqlTransaction has completed; it is no longer usable along with a crash signature which includes a call to the ZombieCheck() function.

Click on the instance name in SQL server management studio and then upon selecting properties, one can set the XACT abort configuration option. Thereafter, choose the Connection page, and then in the Default Connection Options list, as displayed in the screenshot below, xact abort option would reflect at the end of the list:

Microsoft Dynamics NAV - Xact Abort

Upon enabling the zact abort as one of the default connection option in SQL server, the server engine on its own would roll back current trasnactions entirely, if a Transact-SQL statement; the one executed by relevant SQL connection thorew an error. In a scenario where this option is turned off then there are chances that a few servers may not force a complete rollback, but it purely depends on the severity of the error. Settings as mentioned in the above screenshot is all about defining the default for all connections, however; this default may be overridden at connection time on a per connection basis if the developer at the initial stage has included code to obviously – to enable this option to be set either of the ways.

When it comes to Microsoft Dynamics NAV 2013 R2 and Microsoft Dynamics NAV 2015, our developers, never specify any particular setting for "xact abort" while estabilishing a connection to SQL Server and hence; it picks up the default specified at the SQL Server instance level (as displayed in the screenshot above). Usually this works out to be fine, but at some instances reports have been foud that Micrsoft Dynanmics NAV client may crash – throwing an error – "This SqlTransaction has completed; it is no longer usable". Here, to add, on the Windows Event Log on the Micrsoft Dynamics NAV middle tier machine you may come across a message and stacktrace posted by Microsoft Dynamics NAV Server:

Type: System.InvalidOperationException
Message: This SqlTransaction has completed; it is no longer usable.
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

There are chances that the scenarios where one may come across above mentioned errors may differ at time. However; the factor that remains common is that xact abort connection setting that is enabled for relevant SQL Server instance. Forthcoming versions of Microsoft Dynamics NAV are likely to add a code specially to disable xact abort for all Microsoft Dynamics NAV sessions at connection time. Till that time, for Microsoft Dynamics NAV 2013 R2 and Microsoft Dynamics NAV 2015, the best thing would be to turn off the xact abort option at the SQL instance level if no other databases on the same SQL Server instance, needs it.

There might be a case where some other application database, though on the same SQLServer instance, needs to turn on the default connection option for xact abort, due to which you are encountering the aforesaid issue, please consider moving the Microsoft Dynamics NAV database to a separate instance of SQL Server.

About Author:

Chirag Shivalker is Head of Content Team at Hi-Tech Outsourcing Services. With a decade long experience in technology writing and trend analysis Chirag is an expert in technology and technological trends along with business writing.


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: