HADR_FILESTREAM_FILE_CLOSE Wait Type Explained

The Always On FILESTREAM transport manager maintains a dedicated thread that handles FILESTREAM data replication between availability group replicas. When FILESTREAM data changes on the primary replica, the transport manager must transfer these changes to secondary replicas while maintaining file handle lifecycle management.

This wait type surfaces when the transport manager thread reaches the file handle cleanup phase. SQL Server maintains internal file handles for each FILESTREAM object during replication operations. The cleanup process involves flushing any pending I/O operations, releasing memory buffers associated with the file handle, and notifying the secondary replica that the file operation completed successfully.

In SQL Server 2012 through 2014, this wait was more frequent due to aggressive handle cleanup policies. SQL Server 2016 introduced optimizations that batch file handle operations, reducing the frequency but potentially increasing individual wait durations. SQL Server 2019 and later versions implement asynchronous handle closure for non-critical operations, which moved some of these waits to background threads.

The wait duration correlates directly with storage subsystem performance, network latency between replicas, and the size of FILESTREAM objects being processed. Large FILESTREAM files or high-frequency FILESTREAM operations amplify this wait type. Secondary replicas in asynchronous commit mode experience higher wait times because the primary doesn't wait for acknowledgment before proceeding to the next file operation.

-- Check current FILESTREAM-related waits and their frequency
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms / NULLIF(waiting_tasks_count, 0) as avg_wait_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type LIKE 'HADR_FILESTREAM%'
ORDER BY wait_time_ms DESC;

-- Identify FILESTREAM activity in Always On replicas
SELECT 
    ar.replica_server_name,
    ar.availability_mode_desc,
    ar.failover_mode_desc,
    adc.database_name,
    ars.role_desc,
    ars.synchronization_state_desc,
    ars.log_send_queue_size,
    ars.redo_queue_size
FROM sys.availability_replicas ar
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id
JOIN sys.availability_databases_cluster adc ON ar.group_id = adc.group_id
WHERE EXISTS (
    SELECT 1 FROM sys.master_files mf 
    WHERE mf.type = 2 -- FILESTREAM
    AND DB_NAME(mf.database_id) = adc.database_name
);

-- Monitor active FILESTREAM file operations
SELECT 
    session_id,
    request_id,
    wait_type,
    wait_time,
    blocking_session_id,
    wait_resource,
    command,
    percent_complete
FROM sys.dm_exec_requests 
WHERE wait_type = 'HADR_FILESTREAM_FILE_CLOSE'
   OR command LIKE '%FILESTREAM%';

-- Check FILESTREAM database configuration and usage
-- Run this in the context of each FILESTREAM database
SELECT 
    DB_NAME() as database_name,
    df.name as filegroup_name,
    df.physical_name,
    df.size * 8 / 1024 as size_mb,
    df.growth,
    df.is_percent_growth
FROM sys.database_files df
WHERE df.type = 2; -- FILESTREAM

-- Analyze replication performance for FILESTREAM databases
SELECT 
    ag.name as ag_name,
    ar.replica_server_name,
    db.database_name,
    drs.log_send_rate,
    drs.redo_rate,
    drs.log_send_queue_size,
    drs.redo_queue_size
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_database_replica_states drs ON ar.replica_id = drs.replica_id
JOIN sys.availability_databases_cluster db ON ag.group_id = db.group_id
ORDER BY drs.log_send_queue_size DESC;

Increase FILESTREAM handle timeout to reduce premature closures

-- Modify database FILESTREAM settings (SQL Server 2016+)
-- Test in development first - affects all FILESTREAM operations
ALTER DATABASE [YourDatabaseName]
SET FILESTREAM (
    NON_TRANSACTED_ACCESS = FULL,
    DIRECTORY_NAME = N'YourDirectoryName'
);

-- Requires service restart to take full effect
-- Monitor wait stats after implementation

Optimize FILESTREAM file group configuration

-- Add multiple FILESTREAM containers to distribute I/O load
-- Test container paths exist and have proper permissions
ALTER DATABASE [YourDatabaseName]
ADD FILE (
    NAME = 'FileStream_Data_02',
    FILENAME = 'D:\FileStreamData02'
) TO FILEGROUP [FileStreamGroup];

-- Add third container if I/O contention persists
ALTER DATABASE [YourDatabaseName] 
ADD FILE (
    NAME = 'FileStream_Data_03',
    FILENAME = 'E:\FileStreamData03'  
) TO FILEGROUP [FileStreamGroup];

Configure replica-specific FILESTREAM optimization

-- Set secondary replica to asynchronous for FILESTREAM-heavy workloads
-- WARNING: This affects durability guarantees
ALTER AVAILABILITY GROUP [YourAGName]
MODIFY REPLICA ON N'SecondaryServerName'
WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);

-- Alternative: Increase session timeout to allow longer file operations
ALTER AVAILABILITY GROUP [YourAGName] 
MODIFY REPLICA ON N'SecondaryServerName'
WITH (SESSION_TIMEOUT = 20); -- Increase from default 10 seconds

Enable FILESTREAM monitoring and alerting

-- Create monitoring procedure for FILESTREAM waits
CREATE OR ALTER PROCEDURE dbo.CheckFILESTREAMWaits
AS
BEGIN
    DECLARE @threshold_ms INT = 5000; -- Alert if average wait > 5 seconds
    
    SELECT 
        'FILESTREAM Wait Alert' as Alert_Type,
        wait_time_ms / NULLIF(waiting_tasks_count, 0) as avg_wait_ms,
        waiting_tasks_count,
        GETDATE() as check_time
    FROM sys.dm_os_wait_stats 
    WHERE wait_type = 'HADR_FILESTREAM_FILE_CLOSE'
      AND wait_time_ms / NULLIF(waiting_tasks_count, 0) > @threshold_ms;
END;

Configure dedicated storage for FILESTREAM data with sufficient IOPS capacity, separate from transaction log drives. Network bandwidth between replicas must accommodate peak FILESTREAM transfer rates, typically requiring 10Gb connections for production workloads.

Set realistic session timeout values in availability group configuration. Default 10-second timeouts cause unnecessary failovers when processing large FILESTREAM objects. Increase to 20-30 seconds for FILESTREAM-heavy databases.

Implement FILESTREAM garbage collection scheduling during maintenance windows. Use sys.sp_filestream_force_garbage_collection to clean up orphaned handles before they accumulate. Schedule this weekly for databases with high FILESTREAM turnover.

Monitor FILESTREAM container disk space and configure multiple containers across different drives to distribute I/O load. Single container configurations create bottlenecks during replica synchronization.

Consider FILESTREAM alternatives like Azure Blob Storage integration for new applications. Always On replication overhead makes FILESTREAM less suitable for high-frequency file operations in distributed architectures.