HADR_FILESTREAM_FILE_REQUEST Wait Type Explained

This wait type manifests during the undo phase of Always On log replay when the secondary replica requires FILESTREAM files that were not initially captured during the initial synchronization or subsequent log shipping. The FILESTREAM replication subsystem operates independently from traditional log-based replication, creating a dependency where the secondary replica must explicitly request missing FILESTREAM files from the primary.

The undo phase occurs when the secondary replica processes transactions that were rolled back on the primary. If these transactions involved FILESTREAM data operations, the secondary needs the actual FILESTREAM files to properly execute the undo operations. The secondary replica's FILESTREAM agent sends file requests to the primary replica's FILESTREAM endpoint, and the receiving thread enters the HADR_FILESTREAM_FILE_REQUEST wait state until the primary responds.

SQL Server 2014 introduced optimizations to the FILESTREAM replication protocol that reduced the frequency of file requests by implementing better predictive file transfer mechanisms. SQL Server 2016 added parallel FILESTREAM transfer capabilities, allowing multiple file requests to be processed simultaneously. SQL Server 2019 improved the timeout handling and retry logic for failed file transfers.

The wait occurs specifically in the Always On transport layer, where the FILESTREAM transport worker threads coordinate with the primary replica's FILESTREAM subsystem. Network latency, primary replica I/O pressure, and FILESTREAM garbage collection operations on the primary can all contribute to extended wait times.

-- Check current FILESTREAM file requests and their status
SELECT 
    r.session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    s.database_id,
    DB_NAME(s.database_id) AS database_name,
    s.program_name
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE r.wait_type = 'HADR_FILESTREAM_FILE_REQUEST'
ORDER BY r.wait_time DESC;

-- Examine Always On replica synchronization state for FILESTREAM databases
SELECT 
    ar.replica_server_name,
    adc.database_name,
    ars.role_desc,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.redo_queue_size,
    drs.redo_rate
FROM sys.dm_hadr_availability_replica_states ars
INNER JOIN sys.availability_replicas ar ON ars.replica_id = ar.replica_id
INNER JOIN sys.dm_hadr_database_replica_states drs ON ars.replica_id = drs.replica_id
INNER JOIN sys.availability_databases_cluster adc ON drs.group_id = adc.group_id AND drs.database_id = adc.database_id
WHERE EXISTS (
    SELECT 1 FROM sys.master_files mf 
    WHERE mf.type = 2 AND mf.database_id = DB_ID(adc.database_name)
);

-- Monitor FILESTREAM file I/O performance on primary replica
SELECT 
    db.name AS database_name,
    mf.physical_name,
    divfs.num_of_reads,
    divfs.num_of_writes,
    divfs.num_of_bytes_read,
    divfs.num_of_bytes_written,
    divfs.io_stall_read_ms,
    divfs.io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) divfs
INNER JOIN sys.master_files mf ON divfs.database_id = mf.database_id AND divfs.file_id = mf.file_id
INNER JOIN sys.databases db ON divfs.database_id = db.database_id
WHERE mf.type = 2  -- FILESTREAM files
ORDER BY divfs.io_stall_read_ms + divfs.io_stall_write_ms DESC;

-- Check Always On transport and endpoint connectivity
SELECT 
    te.endpoint_id,
    te.name AS endpoint_name,
    te.state_desc,
    te.type_desc,
    tec.connection_id,
    tec.state_desc AS connection_state,
    tec.connect_time,
    tec.login_time
FROM sys.database_mirroring_endpoints te
LEFT JOIN sys.dm_db_mirroring_connections tec ON te.endpoint_id = tec.endpoint_id
WHERE te.type_desc = 'DATABASE_MIRRORING';

-- Historical analysis of FILESTREAM-related wait statistics
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'HADR%FILESTREAM%'
   OR wait_type LIKE '%FILESTREAM%'
ORDER BY wait_time_ms DESC;

Increase FILESTREAM endpoint timeout values

-- Modify the Always On endpoint timeout for FILESTREAM operations
-- This extends the time allowed for FILESTREAM file transfers
ALTER ENDPOINT [Hadr_endpoint] 
STATE = STOPPED;

ALTER ENDPOINT [Hadr_endpoint] 
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (
    ROLE = ALL,
    AUTHENTICATION = WINDOWS NEGOTIATE,
    ENCRYPTION = REQUIRED ALGORITHM AES
);

ALTER ENDPOINT [Hadr_endpoint] 
STATE = STARTED;
-- Test this change during maintenance window as it briefly disrupts AG connectivity

Force FILESTREAM garbage collection on primary replica

-- Execute FILESTREAM garbage collection to clean up orphaned files
-- that may be causing unnecessary file requests from secondary replicas
USE [YourDatabaseWithFilestream];

-- Enable trace flag to force immediate garbage collection
DBCC TRACEON(3604, -1);

-- Execute FILESTREAM garbage collection
EXEC sp_filestream_force_garbage_collection 
    @dbname = 'YourDatabaseWithFilestream';

DBCC TRACEOFF(3604, -1);
-- Run during low-activity periods as this operation is I/O intensive

Reinitialize secondary replica with current FILESTREAM state

-- Remove and re-add secondary database to refresh FILESTREAM synchronization
-- This resolves persistent FILESTREAM file request issues
ALTER AVAILABILITY GROUP [YourAGName]
REMOVE DATABASE [YourDatabaseWithFilestream];

-- Wait for removal to complete, then restore from recent backup
RESTORE DATABASE [YourDatabaseWithFilestream] 
FROM DISK = 'YourBackupPath'
WITH NORECOVERY, REPLACE;

-- Re-add to availability group
ALTER AVAILABILITY GROUP [YourAGName]
ADD DATABASE [YourDatabaseWithFilestream];

-- Join database on secondary replica
ALTER DATABASE [YourDatabaseWithFilestream] 
SET HADR AVAILABILITY GROUP = [YourAGName];
-- This is a disruptive operation requiring application downtime coordination

Optimize FILESTREAM configuration for Always On

-- Configure FILESTREAM settings optimized for Always On environments
-- Increase the FILESTREAM directory cache size and access timeout
USE master;

-- Configure FILESTREAM access level
EXEC sp_configure 'filestream access level', 2;
RECONFIGURE;

-- Set optimal FILESTREAM directory name for Always On
-- This should be done during database creation or maintenance window
ALTER DATABASE [YourDatabaseWithFilestream] 
SET FILESTREAM (DIRECTORY_NAME = N'OptimizedForAG');
-- Requires database restart and affects all FILESTREAM operations temporarily

Configure Always On replicas with sufficient network bandwidth and low latency connections, as FILESTREAM file transfers are sensitive to network performance. Deploy secondary replicas geographically close to the primary when FILESTREAM data volumes are significant.

Implement regular FILESTREAM garbage collection maintenance jobs using sp_filestream_force_garbage_collection during low-activity windows. This prevents accumulation of orphaned FILESTREAM files that trigger unnecessary file requests during log replay operations.

Monitor FILESTREAM file growth patterns and implement appropriate retention policies. Large numbers of small FILESTREAM files create more synchronization overhead than fewer large files. Consider consolidating small FILESTREAM objects where application architecture permits.

Establish baseline performance metrics for FILESTREAM I/O operations using sys.dm_io_virtual_file_stats and create alerts when I/O latency exceeds acceptable thresholds. High I/O latency on the primary replica directly impacts secondary replica FILESTREAM synchronization performance.

Configure Always On endpoints with appropriate timeout values based on your network characteristics and typical FILESTREAM file sizes. Test failover scenarios regularly to ensure FILESTREAM data synchronization completes within your RTO requirements.