SBX - Search With Button

SBX - Forum Post Title

SQL condition "63 Query timeout expired" just comes and goes

Microsoft Dynamics SL Forum

Erich Strelow asked a question on 24 Oct 2017 7:36 AM
My Badges

Question Status

Unanswered

A couple of times I've encountered a situation in which SQL Server yields the following condition:

SQL Server Message 63: [Microsoft][SQL Server Native Client 10.0]Query timeout expired

This happening with any action update-worthy under Dynamics SL 2011.

I've checked out some tips, like KB2551478. I have no FRX installation. There are some custom triggers setup, but all of them have the "set no count" statement.

We also scan for deadlocks, but none appears. What strikes me most is that this behavior stops after some 40-90 minutes. I'm still without a clue on what started or what stopped this.

We've tried the following:

  • Restarting the SQL service.
  • Kill some idle SQL sessions. We run an Application Server that seems to leave lingering sessions. 
  • Enable/Disable selected triggers.
  • We also check out our SQL server health. No indication of overload or system errors.

After all this actions, most of them nonsense guesses, the behavior is still the same. Then after a while, the problem stops and back to normal again.

Anyone has found some light on this?

Reply
Mark E responded on 7 Jan 2019 10:36 AM
My Badges

Wondering if you found a resolution to this issue?  Just randomly started to happen at one of my installs, trying to release AP batches.  there are some custom triggers and procs, but all have the SET NOCOUNT ON set, and none of them were recently modified.  Running SL 2015 CU2 since 11/2018, and this just started today out of nowhere.

Reply
Mark E responded on 7 Jan 2019 10:58 AM
My Badges

Just resolved this one on my end, but not sure why the error started occurring, or why it never happened before.  In the System DB, there are standard triggers on the AcctSub and AcctXRef tables.  None of those triggers contained the SET NOCOUNT ON switch.  Once I added to all the triggers (there are 3 in each table per Company), the problem went away.  I took a look at some other installs, and the SET NOCOUNT ON is not part of those triggers in those databases.  Weird....

Reply
Erich Strelow responded on 8 Jan 2019 6:14 AM
My Badges

I haven't found an answer. The Query Timeout still occurs every 3 months or so. I'm going to check out the triggers in the System DB you mention, since clearly I have overlooked them.

Reply
Brian_IL responded on 9 May 2019 6:21 PM
My Badges

I had the same issue with an SL2011 installation, where they were unable to release a GL batch once they had one batch hang up with the SQL Query Timeout - SQL Message 63; also seen in SQL as SqlState = HYT00 NativeError = 0 err=63. Restarting SQL and clearing orphaned sessions from the Access table still left them unable to release the GL batch. Clearing the WrkRelease table of the record for this batch (normally done by stored proc pp_01400) also had no effect.

They resolved the problem by adding the "Set NOCount ON" command to three triggers that existed on the AcctXref and AcctSub tables in the SL System DB: sDeleteAcctXref_dbname, sInsertAcctXref_dbname, sUpdateAcctXref_dbname, and similar trigger names on AcctSub. 

Like Mark's observation, there seems to be no reason why this issue suddenly appeared. Altering the 6 triggers defined on these 2 tables did resolve the issue.

Reply

SBX - Two Col Forum

SBX - Migrated JS