HADR_FILESTREAM_BLOCK_FLUSH Wait Type Explained

The Always On transport manager handles the replication of transaction log records from primary to secondary replicas. For databases containing FILESTREAM data, the transport manager must coordinate both the transaction log records and the associated FILESTREAM file data. When a transaction modifies FILESTREAM data on the primary, the log record contains metadata while the actual file data is streamed separately.

The HADR_FILESTREAM_BLOCK_FLUSH wait emerges during the log block hardening process when the transport manager encounters a FILESTREAM-related log record that requires completion of the associated file data transfer before the log block can be marked as processed. The transport manager spawns dedicated worker threads to handle FILESTREAM data streaming, and these workers must signal completion before the log hardening thread can proceed.

This wait specifically manifests in the log capture thread on the primary replica, not on secondaries. The thread processing outbound log records encounters a FILESTREAM log entry and must wait for the FILESTREAM transport component to finish moving the corresponding file data to all synchronous secondaries. The wait duration directly correlates to network throughput between replicas and disk I/O performance of the FILESTREAM file groups on secondary replicas.

SQL Server 2016 introduced improvements to FILESTREAM replication by implementing more efficient chunk-based streaming and better parallelization of file transfers. SQL Server 2019 added enhanced monitoring capabilities and reduced the frequency of transport manager blocking by implementing asynchronous FILESTREAM metadata operations where possible.

-- Check current HADR_FILESTREAM_BLOCK_FLUSH waits and their duration
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 = 'HADR_FILESTREAM_BLOCK_FLUSH'
    AND waiting_tasks_count > 0;

-- Identify databases with FILESTREAM in availability groups
SELECT 
    db.name AS database_name,
    ag.name AS availability_group_name,
    ar.replica_server_name,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    fs.type_desc AS filestream_type
FROM sys.databases db
JOIN sys.dm_hadr_database_replica_states drs ON db.database_id = drs.database_id
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id
JOIN sys.database_files fs ON db.database_id = DB_ID(db.name)
WHERE fs.type = 2 -- FILESTREAM data files
    AND db.replica_id IS NOT NULL;

-- Monitor FILESTREAM performance counters for availability groups
SELECT 
    instance_name,
    counter_name,
    cntr_value,
    cntr_type
FROM sys.dm_os_performance_counters 
WHERE object_name LIKE '%FILESTREAM%'
    OR (object_name LIKE '%Availability Replica%' 
        AND counter_name LIKE '%Bytes%/sec%')
ORDER BY instance_name, counter_name;

-- Check for FILESTREAM-related blocking in availability group transport
SELECT 
    s.session_id,
    r.command,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    t.text AS sql_text
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type LIKE '%HADR%FILESTREAM%'
    OR (r.command LIKE '%HADR%' AND t.text LIKE '%FILESTREAM%');

-- Analyze data movement performance across replicas
SELECT 
    ar.replica_server_name,
    drs.database_id,
    DB_NAME(drs.database_id) AS database_name,
    drs.log_send_queue_size,
    drs.log_send_rate,
    drs.redo_queue_size,
    drs.redo_rate
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
WHERE drs.log_send_queue_size > 10240; -- 10MB threshold

Increase FILESTREAM Network Buffer Size This script increases the network buffer size for FILESTREAM operations to reduce the frequency of small network operations.

-- WARNING: Requires restart of SQL Server service
-- Test in development environment first
-- Increases buffer from default 65536 to 1MB
EXEC sp_configure 'filestream access level', 2;
RECONFIGURE;

-- Note: The actual buffer size increase requires registry modification
-- HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\FILESTREAM
-- Set "NetworkBufferSize" DWORD to 1048576 (1MB)
-- Restart SQL Server service after registry change

Optimize FILESTREAM File Group Placement Moves FILESTREAM containers to faster storage to reduce I/O wait times on secondary replicas.

-- Identify current FILESTREAM file locations
SELECT 
    db.name AS database_name,
    df.name AS logical_name,
    df.physical_name,
    df.size * 8 / 1024 AS size_mb
FROM sys.databases db
JOIN sys.master_files df ON db.database_id = df.database_id
WHERE df.type = 2; -- FILESTREAM files

-- Example: Add new FILESTREAM container on faster storage
-- Replace paths with actual fast storage locations
-- ALTER DATABASE [YourDatabase] 
-- ADD FILE (
--     NAME = 'YourDB_FS_Fast',
--     FILENAME = 'F:\FastStorage\YourDB_FS_Fast'
-- ) TO FILEGROUP [YourFS_FileGroup];

Configure Availability Group for FILESTREAM Optimization Adjusts availability group settings to better handle FILESTREAM workloads.

-- Set secondary replica to asynchronous for FILESTREAM-heavy workloads
-- This reduces blocking on primary but sacrifices some consistency
-- Only use if business requirements allow potential data loss

-- ALTER AVAILABILITY GROUP [YourAG]
-- MODIFY REPLICA ON N'SecondaryServer'
-- WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);

-- Alternative: Increase session timeout to handle longer FILESTREAM operations
ALTER AVAILABILITY GROUP [YourAG]
MODIFY REPLICA ON N'SecondaryServer'
WITH (SESSION_TIMEOUT = 20); -- Increased from default 10 seconds

Monitor and Alert on FILESTREAM Transport Issues Creates a monitoring job to detect recurring HADR_FILESTREAM_BLOCK_FLUSH waits.

-- Create alert for sustained FILESTREAM transport waits
-- Deploy as SQL Agent job running every 5 minutes
DECLARE @wait_time_threshold INT = 30000; -- 30 seconds
DECLARE @wait_count_threshold INT = 10;

IF EXISTS (
    SELECT 1 
    FROM sys.dm_os_wait_stats 
    WHERE wait_type = 'HADR_FILESTREAM_BLOCK_FLUSH'
        AND wait_time_ms > @wait_time_threshold
        AND waiting_tasks_count > @wait_count_threshold
)
BEGIN
    -- Log to custom monitoring table or send alert
    RAISERROR('HADR FILESTREAM transport experiencing delays', 16, 1);
END;

Configure FILESTREAM containers on high-performance storage, preferably NVMe SSDs, and ensure secondary replicas have equivalent or better I/O capabilities than the primary. Network connectivity between replicas must support sustained high bandwidth, minimum 10 Gbps for FILESTREAM-heavy workloads.

Set realistic session timeout values in availability group configuration, starting with 20 seconds for FILESTREAM databases instead of the default 10 seconds. Monitor the log_send_rate and redo_rate metrics in sys.dm_hadr_database_replica_states to establish baseline performance and detect degradation.

Consider asynchronous commit mode for secondary replicas when FILESTREAM data represents a significant portion of transaction log activity and business requirements permit potential data loss during failover. Implement connection pooling and batch FILESTREAM operations to reduce the frequency of small file transfers that amplify transport manager overhead.

Establish monitoring for the log_send_queue_size and redo_queue_size metrics, as sustained growth indicates FILESTREAM transport bottlenecks. Deploy network monitoring between replica servers to detect bandwidth saturation or intermittent connectivity issues that manifest as FILESTREAM transport delays.