Как автоматически завершать заблокированные сессии по таймауту в MSSQL с логированием

Для автоматического завершения заблокированных сессий по таймауту с логированием действий используйте следующее решение:

Скрипт для завершения сессий и логирования:

sql
DECLARE @KillLog TABLE (
    SPID INT,
    DatabaseName NVARCHAR(128),
    LoginName NVARCHAR(128),
    HostName NVARCHAR(128),
    ProgramName NVARCHAR(128),
    WaitTimeMS INT,
    KillTime DATETIME DEFAULT GETDATE(),
    SQLText NVARCHAR(MAX)
)

DECLARE @spid INT, @sql_handle VARBINARY(64), @dbid INT

DECLARE cur CURSOR FOR
SELECT 
    p.blocked, 
    p.sql_handle, 
    p.dbid
FROM sys.sysprocesses p
WHERE 
    p.blocked > 0 
    AND p.waittime > 300000 -- 5 минут

OPEN cur
FETCH NEXT FROM cur INTO @spid, @sql_handle, @dbid

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @KillLog (SPID, DatabaseName, LoginName, HostName, ProgramName, WaitTimeMS, SQLText)
    SELECT
        @spid,
        DB_NAME(@dbid),
        loginame,
        hostname,
        program_name,
        waittime,
        (SELECT text FROM sys.dm_exec_sql_text(@sql_handle))
    FROM sys.sysprocesses WHERE spid = @spid

    EXEC('KILL ' + @spid)
    
    FETCH NEXT FROM cur INTO @spid, @sql_handle, @dbid
END

CLOSE cur
DEALLOCATE cur

-- Сохранение лога в постоянную таблицу
INSERT INTO dbo.BlockedSessionsLog 
SELECT * FROM @KillLog

Создание таблицы для логов:

CREATE TABLE dbo.BlockedSessionsLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    SPID INT NOT NULL,
    DatabaseName NVARCHAR(128) NULL,
    LoginName NVARCHAR(128) NULL,
    HostName NVARCHAR(128) NULL,
    ProgramName NVARCHAR(128) NULL,
    WaitTimeMS INT NULL,
    KillTime DATETIME NOT NULL DEFAULT GETDATE(),
    SQLText NVARCHAR(MAX) NULL
)

Автоматизация через SQL Server Agent:

1. Создайте новое задание в SQL Server Agent
2. Добавьте шаг с типом «Transact-SQL script» и вставьте скрипт из пункта 1
3. Настройте расписание (например, каждые 5 минут)
4. Убедитесь, что учетная запись агента имеет права:
   • VIEW SERVER STATE
   • KILL разрешение на процессы
   • Запись в таблицу логов

Мониторинг и анализ:

Для просмотра снятых блокировок:

SELECT * FROM dbo.BlockedSessionsLog
ORDER BY KillTime DESC

Ключевые компоненты логирования:

1. Информация о сессии:
   • Идентификатор процесса (SPID)
   • Имя базы данных
   • Логин и хост
   • Название программы
2. Детали блокировки:
   • Время ожидания (мс)
   • Текст последнего SQL-запроса
   • Время завершения
3. Источник данных:
   • sys.sysprocesses для идентификации заблокированных сессий
   • sys.dm_exec_sql_text для получения текста запроса
   • Временная таблица для промежуточного хранения

Важные замечания:

• Настройте порог waittime согласно требованиям (в примере 300 000 мс = 5 минут)
• Регулярно архивируйте таблицу логов при большом объеме данных
• Добавьте индексы на таблицу логов для ускорения запросов:
• sql
• CREATE INDEX IX_KillTime ON dbo.BlockedSessionsLog(KillTime)
• CREATE INDEX IX_SPID ON dbo.BlockedSessionsLog(SPID)
• Для дополнительного анализа подключите оповещения на основе данных в таблице логов

Это решение обеспечивает прозрачный контроль над автоматическим завершением заблокированных сессий с детальным аудитом действий для последующего анализа проблем производительности