Предисловие
Нередко возникают ситуации, когда какое-то приложение зачем-то долго держит подключение к базе данных. Вроде бы мелочь, но если такой софт делает несколько подключений или, что еще хуже, таких приложений несколько, то лучше с этим как-то бороться.
Данная статья не является руководством. В ней я хотел просто показать возможные решения данной проблемы. Буду рад, если предложат альтернативные решения.
Решение
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
