SQL SERVER – Always On Availability Group Failover Generating Dump. Error: 17066: SQL Server Assertion: HadrFstrVnnUtils.cpp:479

One of my clients was using Always On availability group. They were having 3 nodes Always On AG setup. For simplicity, let’s call them Node1, Node2 and Node3. The first two nodes were in one subnet and node3 was in separate subnet. Let us learn about availability group failover.

The Problem

Client informed that they are not able to fail over the AG from first subnet (node1 & node2) to the second subnet (node3). On the other hand, the failover works fine between node1 and node2 (same subnet)

Initially I thought it’s due to misconfiguration of their network, but it was looking OK. When an attempt was made to fail over the AG group, it comes to “Resolving” state on all replicas. When I observed further, a dump file was generated on node3.

Here is my earlier blog for dump related issues SQL SERVER – Too Many SQLDump Files Consuming a Lot of Disk Space. What Should You Do?

***Stack Dump being sent to xxx\SQLDump0001.txt
* *****************************************************
*
* BEGIN STACK DUMP:
* 12/14/15 09:57:47 spid 52
*
* Location: HadrFstrVnnUtils.cpp:479
* Expression: SUCCEEDED (hr)
* SPID: 52
* Process ID: 5412

Followed by below message

Error: 17066, Severity: 16, State: 1.
SQL Server Assertion: File: , line=479 Failed Assertion = ‘SUCCEEDED (hr)’. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

I had no idea what HadrFstrVnnUtils is. When I broke it down the words it comes as Hadr Fstr Vnn Utils. Still, I had no clue about it. I know Hadr is the earlier name for Always On and now it looks like Fstr = File Stream. If you can find what VNN is, please share and educate me.

I asked them to check SQL Server configuration manager for setting related to Filestream and it turned out that the setting was disabled on Node3 and was enabled on Node1 and Node2.

WORKAROUND/SOLUTION

• Enable FilesStream on each replica and set ‘filestream access level’ to 2

or

• Disable on all replicas and recreate the AG.

Here is the documentation about the steps to enable / change setting of Filestream. Read the details here.

(The steps below are taken from the link)

To enable and change FILESTREAM settings

1. On the Start menu, point to All Programs, point to SQL Server 2016, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the list of services, right-click SQL Server Services, and then click Open.
3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
4. Right-click the instance, and then click Properties.
5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
6. Select the Enable FILESTREAM for Transact-SQL access check box.
7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
8. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data.
9. Click Apply.
10. In SQL Server Management Studio, click New Query to display the Query Editor.
11. In Query Editor, enter the following Transact-SQL code:

    EXEC sp_configure filestream_access_level, 2
    RECONFIGURE

12. Click Execute.
13. Restart the SQL Server service. We have to restart the SQL Server after enabling filestream.

Reference: Pinal Dave (https://blog.sqlauthority.com)