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

Передмова
Нерідко виникають ситуації, коли будь-який додаток навіщо довго тримає підключення до бази даних. Начебто дрібниця, але якщо такий софт робить кілька підключень або, що ще гірше, таких додатків кілька, то краще з цим якось боротися.
Дана стаття не є інструкцією. У ній я хотів просто показати можливі шляхи вирішення даної проблеми. Буду радий, якщо запропонують альтернативні рішення.

Рішення
1) Створимо збережену процедуру, яка закриває всі з'єднання або всі з'єднання конкретного користувача до зазначеної бази даних:
Код
USE [ІМ'Я_БАЗИ_ДАНИХ]
GO

SET ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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, вільний)
+'; '
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 ON ANSI_NULLS
GO

SET ON QUOTED_IDENTIFIER
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, вільний)
+'; '
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
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.