LCK_M_SCH_S_ABORT_BLOCKERS Wait Type Explained

This wait type emerged in SQL Server 2014 with the introduction of online index operations and the WAIT_AT_LOW_PRIORITY option. The lock manager creates this specific wait when an ALTER operation uses the ABORT_AFTER_WAIT = BLOCKERS setting.

The internal process works through SQL Server's lock compatibility matrix. Schema Stability locks conflict with Schema Modification (Sch-M) locks that ALTER operations require. When ABORT_AFTER_WAIT = BLOCKERS is specified, the lock manager enters a specialized code path that actively terminates sessions holding incompatible locks after the specified timeout period expires.

The "ABORT_BLOCKERS" suffix distinguishes this from standard LCK_M_SCH_S waits where the session passively waits. Here, the lock manager's deadlock monitor component actively identifies and kills blocking sessions. The wait persists until either all blocking sessions are terminated or the operation completes.

SQL Server 2016 enhanced this mechanism by improving the accuracy of blocking session identification. SQL Server 2019 added better integration with Query Store to track these forced terminations. SQL Server 2022 improved the lock escalation logic to reduce false positives when identifying truly blocking sessions.

The wait appears in sys.dm_exec_requests during the active termination phase, not during the initial timeout period. This distinguishes it from similar schema lock waits that represent passive waiting states.

-- Identify current sessions experiencing this wait
SELECT 
    r.session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id,
    t.text AS current_sql,
    s.program_name,
    s.login_name
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type = 'LCK_M_SCH_S_ABORT_BLOCKERS';

-- Find ALTER operations using WAIT_AT_LOW_PRIORITY currently running
SELECT 
    r.session_id,
    r.status,
    r.command,
    r.percent_complete,
    t.text AS sql_text,
    r.estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE t.text LIKE '%WAIT_AT_LOW_PRIORITY%'
   OR t.text LIKE '%ABORT_AFTER_WAIT%';

-- Examine schema locks currently held on the target object
SELECT 
    l.resource_type,
    l.resource_database_id,
    l.resource_associated_entity_id,
    l.request_mode,
    l.request_status,
    l.request_session_id,
    s.program_name,
    s.login_name
FROM sys.dm_tran_locks l
INNER JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id
WHERE l.resource_type IN ('OBJECT', 'DATABASE')
  AND l.request_mode LIKE 'Sch%'
ORDER BY l.resource_associated_entity_id, l.request_mode;

-- Check for recently killed sessions due to ABORT_BLOCKERS
SELECT 
    login_time,
    last_request_end_time,
    session_id,
    login_name,
    program_name,
    host_name,
    status
FROM sys.dm_exec_sessions
WHERE last_request_end_time > DATEADD(MINUTE, -10, GETDATE())
  AND status = 'sleeping'
ORDER BY last_request_end_time DESC;

-- Monitor lock timeouts and deadlocks related to schema operations
SELECT 
    database_id,
    object_id,
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Lock Timeouts/sec', 'Number of Deadlocks/sec')
  AND cntr_value > 0;

Identify and manually terminate blocking sessions This script identifies sessions blocking schema operations and provides KILL commands. Execute the KILL statements only after verifying the sessions are safe to terminate.

-- Generate KILL commands for blocking sessions
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 'KILL ' + CAST(blocking_session_id AS NVARCHAR(10)) + ';' + CHAR(13)
FROM sys.dm_exec_requests 
WHERE wait_type = 'LCK_M_SCH_S_ABORT_BLOCKERS'
  AND blocking_session_id > 0
  AND blocking_session_id != @@SPID;

PRINT 'Execute these commands to terminate blocking sessions:';
PRINT @sql;
-- EXEC sp_executesql @sql; -- Uncomment after verification

Modify existing ALTER operation timeout Increases the timeout period for operations already using WAIT_AT_LOW_PRIORITY. This gives more time before sessions are forcibly terminated.

-- Template to modify timeout for running ALTER operations
-- Replace [TableName] and timeout values as needed
/*
ALTER TABLE [YourTableName] 
REBUILD 
WITH (ONLINE = ON, 
      WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 MINUTES, 
                           ABORT_AFTER_WAIT = BLOCKERS));
*/

-- Check current timeout settings in plan cache
SELECT 
    cp.plan_handle,
    st.text,
    cp.usecounts
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.text LIKE '%WAIT_AT_LOW_PRIORITY%'
  AND st.text LIKE '%ABORT_AFTER_WAIT%';

Implement graceful session termination Creates a stored procedure to gracefully handle session termination with proper logging. Use this instead of direct KILL commands.

CREATE PROCEDURE sp_GracefulSessionKill
    @SessionId INT,
    @Reason NVARCHAR(255) = 'Schema operation blocking'
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @LoginName NVARCHAR(128);
    DECLARE @ProgramName NVARCHAR(128);
    
    -- Log the termination
    SELECT @LoginName = login_name, @ProgramName = program_name
    FROM sys.dm_exec_sessions 
    WHERE session_id = @SessionId;
    
    PRINT 'Terminating session ' + CAST(@SessionId AS NVARCHAR(10)) + 
          ' (Login: ' + ISNULL(@LoginName, 'Unknown') + 
          ', Program: ' + ISNULL(@ProgramName, 'Unknown') + 
          ') Reason: ' + @Reason;
    
    -- Terminate the session
    EXEC('KILL ' + @SessionId);
END;

Configure ALTER operations with appropriate WAIT_AT_LOW_PRIORITY settings during maintenance windows when blocking is minimal. Use MAX_DURATION values between 5-15 minutes for most operations, with ABORT_AFTER_WAIT = BLOCKERS only when necessary.

Implement connection pooling limits in applications to prevent excessive long-running connections that hold schema locks. Monitor for applications that maintain open transactions or connections beyond their actual usage period.

Schedule index maintenance operations during low-activity periods using SQL Server Agent jobs with proper retry logic. Avoid running multiple schema-modifying operations simultaneously on the same database.

Create alerts on sys.dm_exec_requests for sessions with wait_type = 'LCK_M_SCH_S_ABORT_BLOCKERS' lasting longer than 5 minutes. This indicates either insufficient timeout periods or unexpectedly heavy blocking activity.

Establish application-level timeouts shorter than database-level WAIT_AT_LOW_PRIORITY timeouts to prevent applications from holding locks during planned maintenance operations. This creates a defense-in-depth approach to lock management.

Use Resource Governor to limit the impact of maintenance operations on production workloads, ensuring that ALTER operations with ABORT_BLOCKERS don't consume excessive resources while terminating blocking sessions.