axinthefield: TempDB Errors in AOS event log after SQL Server Cluster Failover or AlwaysOn Availability Groups Failover

[Logger]

TempDB Errors in AOS event log after SQL Server Cluster Failover or AlwaysOn Availability Groups Failover (Update 8/27/2014)
Michael DeVoeFebruary 27, 2014

If you have ever seen errors in your AOS Event Viewer Logs after a SQL Server Cluster or AlwaysOn Availability Groups Failover and wondered why it is happening here is a brief explanation.



Object Server 01:

[Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot drop the table

'tempdb.DBO.t855_489F061A83074B41907007FFAE3B5D41', because it does not exist or you do not have permission.

DROP TABLE tempdb."DBO".t855_489F061A83074B41907007FFAE3B5D41



Dynamics AX 2012 R2 makes use of "persisted" tables in TempDB in SQL Server. This essentially means that they build Tales in TempDB that "hang around" for a long time. This is a little different than how most processes use TempDB as they usually will create a temporary object, use it, and then drop it within a single transaction. When a SQL Server has a Failover TempDB does not "come with it" to the new server node. The AOS has no idea that the SQL Server has had a failover and still assumes these temporary tables still exist and will try to query them generating these types of errors. The only way to get rid of them is to stop and start the AOS services. It means that those TempDB transactions are not safe through the failover

This was actually corrected in is a pre CU3 hotfix which worked KB 2729496. They added code to check for the TempDB table before querying it and if it is not they rebuild it on the fly (There are other actions but I want to keep this brief). This fix was then rolled into CU6 but due to a regression error it was broken.

For now the best practice on SQL Server Failover would be too manually or use a PowerShell script to automate the recycling all the AOS services after a failover. Agreed this is not a perfect workaround and removes some of the benefits of automatic failover but until a fix is released it will prevent many headaches with your users and AOS event logs filling with errors



UPATE 5/9/2014

Due to the complexity and pervasive nature of this issue we are still in the process of investigating all the potential causes. I will update this BLOG as more information on this issue becomes available.

Update 8/27/2014

This issue has now been corrected. The user will still see an error but AX will clean up the pool so that the user will not see any more errors after the initial error even if there are many tempdb tables in the pool. This does not ensure that the user will never see an error or that a batch job will not fail but guarantee that the AOS does not have to be restarted.

AX 2012 RTM

You will need to contact support and ask for KB 2920058

AX 2012 R2

You will need to contact support and ask for KB 2956617