Client gets "Remote harden of transaction X failed" error when AlwaysOn failover occurs
Question
Thursday, May 29, 2014 3:10 PM
We have an AlwaysOn Availability Group, with 3 nodes, one database, running SQL Server 2012 SP1 on Windows Server 2012 R2. We have a client application written in C++, that uses SQL Native Client (ADO) to access the database. We have been testing our client app in various AlwaysOn failover scenarios. We have observed one odd scenario:
1. We create a table with over 2 million rows
2. Our client app issues a simple delete, to delete all rows in the table
3. After about 20 seconds, we perform a manual failover of the AlwaysOn availability group
4. The client application loses its database connection, as we expect. However, instead of getting the usual error about communication link failure, the client gets this error:
ADO Error = ``80004005
Source = Microsoft SQL Server Native Client ``11.0
Description = Remote harden of transaction ``'user_transaction' (ID ``0x00000000000605ee 0000``:038ca665) started at May ``23 2014 3``:06PM in database ``'ca2503' at LSN (``39246``:``27976``:``17``) failed.
SQLState = ``01000
NativeError = ``3303
I tried this same operation in SQL Server Management Studio, and I got the following messages:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I looked at the SQL Server error severity documentation, and it says that errors in the 20-24 range: "Indicate system problems and are fatal errors, which means that the Database Engine task that is executing a statement or batch is no longer running. The task records information about what occurred and then terminates. In most cases, the application connection to the instance of the Database Engine may also terminate. If this happens, depending on the problem, the application might not be able to reconnect."
Should the SQL Server Native Client report this "Remote harden of transaction failed" error to the client application, or should it translate this message into a known error code that indicates that the client lost its connection (such as "Communication link failure")? If the Native Client should return this "Remote harden" error, can we rely on Native Error = 3303 to know that we've lost our connection?
Thanks,
Heather
All replies (6)
Tuesday, June 3, 2014 9:14 AM âś…Answered
Hi Heather,
I did a few tests and also got error 3303 when the connection is dealing with large amount data modifications. We should be able to rely on this error and catch this error for re-trying the connection.
Hope it helps.
Cheers,
Ying
Friday, May 30, 2014 8:20 AM
Hi HeatherK,
I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
Thank you for your understanding and support.
If you have any feedback on our support, please clickhere.
Regards,
Sofiya Li
Sofiya Li
TechNet Community Support
Friday, May 30, 2014 4:13 PM
A few questions to understand the issue:-
Are you facing issues with this table only or all the tables?
After the failover, what command do you issue from SSMS to check? SELECT * or something else?
Also, do you have any triggers on these tables?
REFERENCE: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d3469de5-683f-425a-8845-4a5ecba18713/a-severe-error-occurred-on-the-current-command-the-results-if-any-should-be-discarded?forum=sqldatabaseengine
Please mark the answer as helpful if i have answered your query. Thanks and Regards, Kartar Rana
Monday, June 2, 2014 3:28 AM
Hi Heather,
Other than the error 3303, do you see any other errors from the application?
The 3303 error occurs when the Local Log records are hardened but the remote harden cannot be completed, normally due to connection lost.
Your test is similar to the **"**Test 2: Primary replica with Manual failover" in below link:
http://sqlwriter.blogspot.com/2014/01/sql-server-2012-alwayson-testing.html
I often see this kind of error in the SQL server ERRORLOG when a failover happens, it should be normal.
I suggest you also test an idle connection and check what the error message is when failover happens and idle connection gets disconnected. I think in this scenario, it should be something like "communication link failure".
Cheers,
Ying
Monday, June 2, 2014 6:11 PM
Hello,
Thanks for your replies. Let me answer your questions first.
1. No, there are no triggers on the tables.
2. We have only seen this 3303 error when a statement that affects many rows is in progress when the failover occurs. Most of the time, we get the "communication link failure" error, or SQL state 08S01. We have also seen SQL state of 42000 and native error of 50000, which means a shutdown is in progress.
3. When we issue the long-running delete in SSMS, that tab ends up disconnected. We don't have to issue any additional statement- the status at the bottom shows disconnected.
So it sounds like this behavior is working as designed, and you don't expect the ADO provider to mask this error. In that case, can we rely on a native error of 3303 to indicate this problem, so that we know that we need to reconnect? Or do we need to issue an additional statement, like select 1, to verify that we have indeed lost our connection?
Thanks,
Heather
Sunday, October 6, 2019 9:45 AM
I faced the same errors and solved the problem , IT happens with SQL server 2014 and 2016 Microsoft will tell you install Cumulative update , But no Need, We know It's a like a friendly Bug. :)
Simply
1- ALTER DATABASE database_name SET HADR RESUME
-- give it 5 mins
2- Failover
-- Give it 5 mins
3- ALTER DATABASE database_name SET HADR RESUME
-- Give it 5 mins
4- DBCC CheckDB(database_name)
-- Done
FIXED :)