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