Главная > SQL Server > Оповещение о наличии длительной блокировки на SQL Server

Оповещение о наличии длительной блокировки на SQL Server

Встал как то вопрос о том чтобы последить за длительными блокировками на SQL Server 2000.
Такие блокировки могут быть как от действительно длительных и очень длительных (возможно "кривых" запросов) процессов, так и от "зависших" пользовательских процессов.
Можно например запустив Enterprise Manager посмотреть Current Activity и увидеть очередь возникших блокировок, иногда EM задумывается на этом процессе неприемлемо долго.
Можно в Query Analyzer выполнить хранимую процедуру sp_who2 и также определить очередь и блокирующий(ие) процессы.
А как сделать так, чтобы ничего не делать? 🙂 — правильно — автоматизировать.

Родился нижележащий скрипт.
Создаем на его основе джоб, периодически выполняем, периодически получаем уведомление на email оповещение о длительной блокировке.

Не продакшен версия конечно, но кому пригодится — черкните.
===================================================================
—Оповещение о наличии длительной блокировки на сервере
— Отправляем email в случае обнаружения длительной блокировки

SET NOCOUNT ON
— Создадим переменные
DECLARE @LimitWaitTime int,
@MaxWaitTime int,
@SPID smallint,
@subject_str varchar(255),
@message_str varchar(255),
@separator_str varchar(1),
@email varchar(128)

— Определим значения переменных
SET @LimitWaitTime=300000 — Максимальное время в милисекундах ожидания в очереди блокировок, при превышении которого отсылаем email (типа признак ДЛИТЕЛЬНОЙ блокировки)
SET @separator_str=CHAR(9) — Символ табуляции
SET @email = ‘events@vesna.ru’

— создаем временную таблицу для хранения информации от sp_who2
IF (object_id(‘tempdb..#t1’) IS not null) DROP TABLE #t1
IF (object_id(‘tempdb..##t2’) IS not null) DROP TABLE ##t2
CREATE TABLE #t1 (SPID smallint, Status varchar(30), Login varchar(128), HostName varchar(128), BlkBy varchar(5), DBName varchar(128), Command varchar(16), CPUTime int, DiskIO bigint, LastBatch varchar(128), ProgramName varchar(128), SPID2 smallint);

— Выполняем и сохраняем результат sp_who2
INSERT INTO #t1 EXECUTE (‘sp_who2’)

— Дополняем результат sp_who2 данными о WaitTime
— Некоторые длинные поля немного урезаем SUBSTRING-ом для приемлемого вида
SELECT
tbl1.SPID,
SUBSTRING(tbl1.Status,1,15) AS Status,
SUBSTRING(Login,1,25) AS Login,
SUBSTRING(tbl1.HostName,1,15) AS HostName,
BlkBy, SUBSTRING(DBName,1,15) AS DBName,
SUBSTRING(Command,1,35) AS Command,
CPUTime,
DiskIO,
SUBSTRING(LastBatch,1,15) AS LastBatch,
SUBSTRING(ProgramName,1,60) AS ProgramName,
WaitTime
INTO ##t2
FROM #t1 AS tbl1 LEFT OUTER JOIN master..sysprocesses AS tbl2 WITH (NOLOCK) ON tbl1.SPID = tbl2.SPID
WHERE tbl1.SPID IN (SELECT BlkBy FROM #t1 WHERE BlkBy not like ‘%.%’) OR BlkBy not like ‘%.%’

— Удаляем временную таблицу
IF (object_id(‘tempdb..#t1’) IS not null) DROP TABLE #t1

— Максимальное время ожидания в очереди блокировок
SELECT @MaxWaitTime=MAX(WaitTime) FROM ##t2
SELECT @MaxWaitTime AS [Максимальное время ожидания (мсек)]

— Блокирующий процесс
SELECT @SPID=SPID FROM ##t2 WHERE BlkBy like ‘%.%’
SELECT @SPID AS [Блокирующий SPID]
SELECT DISTINCT * FROM ##t2

— Отправляем email оповещение об обнаружении ДЛИТЕЛЬНОЙ блокировки
IF (@MaxWaitTime>@LimitWaitTime) BEGIN

— Подготовим текст сообщения
SET @subject_str = ‘SQL Server ‘+@@SERVERNAME+’: Длительная блокировка! (SPID:’+convert(varchar(5),@SPID)+’)’
SET @message_str = ‘Обнаружена длительная блокировка!

Сервер ‘+@@SERVERNAME+’
Идентификатор процесса (SPID) = ‘+convert(varchar(5),@SPID)+’,
Максимальное время ожидания в очереди блокировок = ‘+convert(varchar(5),@MaxWaitTime/60000)+’ мин.,

Примите меры!’

— Отправляем email
EXEC xp_sendmail @recipients = @email,
@query = ‘SELECT * FROM ##t2’,
@subject = @subject_str,
@message = @message_str,
@attachments = ‘sp_who2.txt’,
@attach_results = ‘TRUE’,
@width = 1024,
@separator = @separator_str
—,@no_header= ‘TRUE’
END

— Удаляем временную таблицу
IF (object_id(‘tempdb..##t2’) IS not null) DROP TABLE ##t2
===================================================================

Применимо:
MS SQL Server 2000

Реклама
Рубрики:SQL Server
  1. MaZaY
    29.01.2009 в 15:37

    Спасибо за пост! Жаль, что у нас "кадры" ведутся в 1C, мне придётся творчески переписать скрипт 🙂

  2. Nescoffe
    02.10.2012 в 09:29

    Очень пригодилось, Спасибо за труд!
    Только пришлось исправить символ комментариев «– » и кавычки «‘»

  1. No trackbacks yet.

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

%d такие блоггеры, как: