Автоматическое удаление зависших процессов в MS SQL Server

Предисловие

Нередко возникают ситуации, когда какое-то приложение зачем-то долго держит подключение к базе данных. Вроде бы мелочь, но если такой софт делает несколько подключений или, что еще хуже, таких приложений несколько, то лучше с этим как-то бороться.

Данная статья не является руководством. В ней я хотел просто показать возможные решения данной проблемы. Буду рад, если предложат альтернативные решения.

Решение

1) Cоздадим хранимую процедуру, которая закрывает все соединения или все соединения конкретного пользователя к указанной базе данных:

Код

USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [srv].[KillConnect]
    @databasename nvarchar(255), --БД
    @loginname    nvarchar(255)=NULL  --Логин
AS
BEGIN
    /*
        Удаляет соединения для указанной БД и указаного логина входа
    */
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    if(@databasename is null)
    begin
        ;THROW 50000, 'База данных не задана!', 0;
    end
    else
    begin
        declare @dbid int=db_id(@databasename);

        if(@dbid is NULL)
        begin
            ;THROW 50000, 'Такой базы данных не существует!', 0;
        end
        else if @dbid <= 4
        begin
            ;THROW 50000, 'Удаления подключений к системной БД запрещены!', 0;
        end
        else
        begin
            declare @query nvarchar(max);
            set @query = '';

            select @query=coalesce(@query,',' )
                        +'kill '
                        +convert(varchar, spid)
                        +'; '
            from master..sysprocesses
            where dbid=db_id(@databasename)
            and spid<>@@SPID
            and (loginame=@loginname or @loginname is null);

            if len(@query) > 0
            begin
                begin try
                    exec(@query);
                end try
                begin catch
                end catch
            end
        end
    end
END

GO

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

2) Создадим хранимую процедуру для удаления всех зависших процессов:

Код

USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [srv].[KillFullOldConnect]
AS
BEGIN
    /*
        Удаляет те подключения, последнее выполнение которых было более суток назад.
        Внимание! Системные БД master, tempdb, model и msdb не участвуют в процессе.
        Однако, БД distribution для репликаций будет затронута и это нормально.
    */
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    declare @query nvarchar(max);
    set @query = '';

    select @query=coalesce(@query,',' )
                +'kill '
                +convert(varchar, spid)
                +'; '
    from master..sysprocesses
    where dbid>4
    and [last_batch]<dateadd(day,-1,getdate())
    order by [last_batch]

    if len(@query) > 0
    begin
        begin try
            exec(@query);
        end try
        begin catch
        end catch
    end
END

GO

Данная хранимая процедура удаляет все те подключения, которые последний раз выполнялись более суток назад. Также данная хранимая процедура не затрагивает основные системные базы данных (master, tempdb, model и msdb). Ничего страшного не произойдет, т. к. если будет запрошен доступ, а подключение было отключено, то просто создастся новое подключение для данного приложения с запросившим пользователем.

А теперь хранимую процедуру из п.2 достаточно запускать раз в сутки в задании Агента:

exec [ИМЯ_БАЗЫ_ДАННЫХ].[srv].[KillFullOldConnect];

Лучше, конечно, данный вызов обложить блоком try-catch, чтобы обработать возможные вызовы исключений.

Результат

В данной статье был рассмотрен пример реализации хранимых процедур по закрытию соединения к базе данных (всех или конкретного пользователя) и завершения повисших процессов. Также был рассмотрен пример автоматического ежедневного запуска задания завершения повисших процессов. Это позволяет уменьшить количество "мертвых" подключений к серверу. Удаление всех подключений к базе данных позволяет поменять ей некоторые свойства, а также срочно убить процесс, который создает какую-либо проблему.

Источники:

» sysprocesses
» kill
» db_id
» @@SPID