Error after SQL-Server Update: The prepared statement with handle xyz was not found

Question

In the last weeks we updated two of our customers. Both got a new SQL-Server and both have now this error: "The prepared statement with handle xyz was not found."

The error occures several times throughout the day but not on all mid tier servers. If the error arises the transaction stops. In some cases the mid tier server shows this error for some minutes (not to all users) and works correct some time later. In most cases we have to restart the mid tier service.

This is what changed:

1. both have now a VMWare VM (prior it was a Hardware Server)
2. Windows Server changed from Win 2008 / Win 2012 to Win 2016
3. SQL Server version changed from 2012 to 2017 (customer a) / 2014 to 2016 (customer b)

The error is logged in the Windows Eventlog on mid tier servers that interact with the databases and the users get a appropiate message box. We don't see this at the SQL server. Maybe we don't look at the right log...

Do you have any hint or suggestion or advice what we can do, where we should look, what to change to get rid of this error?

Our customers use MS Dynamics NAV 2013R2.

Thanks in advance Heinrich

Answer 1

You can potentially find the error message through Extended Events. Create and start the following session:

CREATE EVENT SESSION FailedQueries
ON SERVER
 ADD EVENT sqlserver.error_reported
 (
   ACTION 
   (
     sqlserver.client_app_name,
     sqlserver.client_hostname,
     sqlserver.nt_username,
     sqlserver.sql_text
    )
  )
  ADD TARGET package0.asynchronous_file_target
  (
    SET FILENAME = N'C:\temp\FailedQueries.xel',
    METADATAFILE = N'C:\temp\FailedQueries.xem'
  );
GO

ALTER EVENT SESSION FailedQueries ON SERVER STATE = START;
GO

To make sure it is working, run a query that will definitely raise an exception:

SELECT 1/0 FROM sys.objects;

Check the session to make sure this event populated:

;WITH event_data AS 
(
  SELECT data = CONVERT(XML, event_data)
    FROM sys.fn_xe_file_target_read_file(
   'C:\temp\FailedQueries*.xel', 
   'C:\temp\FailedQueries*.xem', 
   NULL, NULL
 )
),
results AS
(
  SELECT 
    [host]      = data.value('(event/action[@name="client_hostname"]/value)[1]','nvarchar(4000)'),
    [app]       = data.value('(event/action[@name="client_app_name"]/value)[1]','nvarchar(4000)'),
    [date/time] = data.value('(event/@timestamp)[1]','datetime2'),
    [query]     = data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)'),
    [error]     = data.value('(event/data[@name="error_number"]/value)[1]','int'),
    [state]     = data.value('(event/data[@name="state"]/value)[1]','tinyint'),
    [message]   = data.value('(event/data[@name="message"]/value)[1]','nvarchar(250)')
  FROM event_data
)
SELECT * FROM results
WHERE error NOT IN (5701,5702,5703)
ORDER BY [date/time] DESC;

Then you can periodically check the output of this query (maybe adding a filter like AND [message] LIKE N'%prepared statement%'). Hopefully that gives you some additional clues.