My home lab setup consists of four servers running in HyperV across two physical hosts. The SQL Server instances are SQLAG101, SQLAG102, SQLAG201, and SQLAG202.
SQLAG101 and SQLAG102 are members of the SQLAG100 Availability Group, and are on the 192.168.0.0/24 network.
SQLAG201 and SQLAG202 are members of the SQLAG200 Availability Group, and are on the 192.168.2.0/24 network.
Traffic is routed between the two subnets, which are both local to my lab (i.e. there is very little latency involved).
SQLDAG is a Distributed Availability Group spanning SQLAG100 and SQLAG200. This has been running well for about 6 months, with automatic failover between the AG member servers, and manual failovers between the two AGs working correctly, and without data loss.
On my test server, I'm seeing the following errors on my Distributed AG Forwarder for a database using FILESTREAM:
The operating system returned the error '2(The system cannot find the file specified.)' while attempting 'CreateFileW' on 'F:\SQLServer\HV2019\FILESTREAM\dag_test_db\dag_test_db_fg_fs_f01\3e6a0757-7405-4ee2-b8a8-df878b8cd7ce\a10e3ae8-922c-4821-904e-7555c031630d\0000008f-000292b0-0006' at 'fsdohdlr.cpp'(2474).
Always On Availability Groups data movement for database 'dag_test_db' has been suspended for the following reason: "system" (Source ID 3; Source string: 'SUSPEND_FROM_CAPTURE'). To resume data movement on the database, you will need to resume the database manually. For information about how to resume an availability database, see SQL Server Books Online.
(love that Books Online reference, btw)
To troubleshoot, I've completely dropped the dag_test_db from the forwarder and the forwarders secondary. I then took a full backup from the primary, and restored it to the forwarder, and rolled forward by restoring log backups as necessary prior to adding the dag_test_db back into the forwarders Availability Group via ALTER DATABASE [dag_test_db] SET HADR AVAILABILITY GROUP = SQLAG200;
Initially, the Availability Group Dashboard for the forwarders AG (SQLAG200) showed the database as being synchronized, but after about an hour the sync state shows NOT SYNCHRONIZING and the sync health reason description shows SUSPEND_FROM_CAPTURE.
Running chkdsk /f on the F: drive reports no errors.
dbcc checkdb (dag_test_db) from the primary returns no errors.
I have written a query to read all the data from filestream; perhaps that will show something interesting. Once I have the results from that test, I'll update the question. The query is using ApplicationIntent=ReadOnly via sqlcmd.exe to connect to the forwarder instance. This is the query, for those interested:
USE [dag_test_db];
GO
DECLARE @d varbinary(max);
DECLARE @f nvarchar(260);
DECLARE cur CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT
[f].[file_data]
, [f].[original_file_name]
FROM [dbo].[files] [f];
OPEN cur;
FETCH NEXT FROM cur INTO @d, @f;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @f + N': ' + CONVERT(nvarchar(11), LEN(@d), 0);
FETCH NEXT FROM cur INTO @d, @f;
END
CLOSE cur;
DEALLOCATE cur;
Perhaps some dashing young Microsoft engineer can help me understand what is causing an error in fsdohdlr.cpp at 2474?
DBCC CHECKDB ([dag_test_db]) WITH NO_INFOMSGS; on the global primary shows no corruption.
Converting the file name in the error message to a decimal LSN, and using fn_dblog on the global primary to display the contents of the log shows:
| Current LSN | Operation | Context | Transaction ID | LogBlockGeneration | Tag Bits | Log Record Fixed Length | Log Record Length | Previous LSN | Flag Bits | Log Reserve | AllocUnitId | AllocUnitName | Page ID | Slot ID | Previous Page LSN | PartitionId | RowFlags | Num Elements | Offset in Row | Modify Size | Checkpoint Begin | CHKPT Begin DB Version | Max XDESID | Num Transactions | Checkpoint End | CHKPT End DB Version | Minimum LSN | Dirty Pages | Oldest Replicated Begin LSN | Next Replicated End LSN | Last Distributed Backup End LSN | Last Distributed End LSN | Repl Min Hold LSN | Server UID | SPID | Beginlog Status | Xact Type | Begin Time | Transaction Name | Transaction SID | Parent Transaction ID | Oldest Active Transaction ID | Xact ID | Xact Node ID | Xact Node Local ID | End AGE | End Time | Transaction Begin | Replicated Records | Oldest Active LSN | Server Name | Database Name | Mark Name | Master XDESID | Master DBID | Preplog Begin LSN | Prepare Time | Virtual Clock | Previous Savepoint | Savepoint Name | Rowbits First Bit | Rowbits Bit Count | Rowbits Bit Value | Number of Locks | Lock Information | LSN before writes | Pages Written | Command Type | Publication ID | Article ID | Partial Status | Command | Byte Offset | New Value | Old Value | New Split Page | Rows Deleted | Bytes Freed | CI Table Id | CI Index Id | NewAllocUnitId | FileGroup ID | Meta Status | File Status | File ID | Physical Name | Logical Name | Format LSN | RowsetId | TextPtr | Column Offset | Flags | Text Size | Offset | Old Size | New Size | Description | Bulk allocated extent count | Bulk RowsetId | Bulk AllocUnitId | Bulk allocation first IAM Page ID | Bulk allocated extent ids | VLFs added | InvalidateCache Id | InvalidateCache keys | CopyVerionInfo Source Page Id | CopyVerionInfo Source Page LSN | CopyVerionInfo Source Slot Id | CopyVerionInfo Source Slot Count | RowLog Contents 0 | RowLog Contents 1 | RowLog Contents 2 | RowLog Contents 3 | RowLog Contents 4 | RowLog Contents 5 | Compression Log Type | Compression Info | PageFormat PageType | PageFormat PageFlags | PageFormat PageLevel | PageFormat PageStat | PageFormat FormatOption | Log Record |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0000008F:000292B0:0006 | LOP_FS_DOWNLEVEL_OP | LCX_NULL | 0000:0022f09f | 0 | 0x0000 | 24 | 360 | 0000008F:000292B0:0005 | 0x0002 | 366 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Operation CREATE;File Id 65537;Name 3e6a0757-7405-4ee2-b8a8-df878b8cd7ce\a10e3ae8-922c-4821-904e-7555c031630d\0000008f-000292b0-0006 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 0x000018008F000000B0920200050002009FF022000000910001004A01010000007000C00001000000010001000000000000000000000046003A005C00530051004C005300650072007600650072005C004D005300530051004C00310035002E004800560032003000310039005C004D005300530051004C005C0044004100540041005C006400610067005F0074006500730074005F00640062005F00660067005F0066007300330065003600610030003700350037002D0037003400300035002D0034006500650032002D0062003800610038002D006400660038003700380062003800630064003700630065005C00610031003000650033006100650038002D0039003200320063002D0034003800320031002D0039003000340065002D003700350035003500630030003300310036003300300064005C00300030003000300030003000380066002D00300030003000320038006600360038002D0030003000310030000001 |
The file referenced in the error message does exist on the forwarder's filesystem:
C:\Users\Hannah>dir F:\SQLServer\HV2019\FILESTREAM\dag_test_db\dag_test_db_fg_fs_f01\3e6a0757-7405-4ee2-b8a8-df878b8cd7ce\a10e3ae8-922c-4821-904e-7555c031630d\0000008f-000292b0-0006
Volume in drive F has no label.
Volume Serial Number is 90BA-CEC3
Directory of F:\SQLServer\HV2019\FILESTREAM\dag_test_db\dag_test_db_fg_fs_f01\3e6a0757-7405-4ee2-b8a8-df878b8cd7ce\a10e3ae8-922c-4821-904e-7555c031630d
04/28/2023 10:27 PM 797,346 0000008f-000292b0-0006
1 File(s) 797,346 bytes
0 Dir(s) 123,663,937,536 bytes free
icacls for the path in question on the forwarder shows:
icacls F:\SQLServer\HV2019\FILESTREAM\dag_test_db\dag_test_db_fg_fs_f01\3e6a0757-7405-4ee2-b8a8-df878b8cd7ce\a10e3ae8-922c-4821-904e-7555c031630d
F:\SQLServer\HV2019\FILESTREAM\dag_test_db\dag_test_db_fg_fs_f01\3e6a0757-7405-4ee2-b8a8-df878b8cd7ce\a10e3ae8-922c-4821-904e-7555c031630d NT SERVICE\MSSQL$HV2019:(I)(OI)(CI)(F)
OWNER RIGHTS:(I)(OI)(CI)(F)
BUILTIN\Administrators:(I)(OI)(CI)(F)
Successfully processed 1 files; Failed processing 0 files
The permissions look good from what I can see - the SQL Server service as full control on the file, and indeed the entire folder structure from F:\SQLServer\HV2019.
