Tips for DBA: Deadlock Event Notifications

Начиная с SQL Server 2005, на службе DBA появилась такая замечательная возможность, как Event Notifications, что в русской версии BOL принято называть уведомлением о событиях. Этот механизм позволяет включить незаметную трассировку системных событий и извлекать информацию о заданных события из очереди для анализа или реакции со стороны администратора. Полный список событий, которые таким образом можно отслеживать, можно найти в статье: События трассировки для использования с уведомлениями о событии

Давайте рассмотрим упрощённый пример организации уведомлений о тупиковой блокировке и сделаем так, чтобы по электронной почте можно было получить письмо с графом блокировок.

Подготовительные действия

Для начала настройте на тестовом сервере Database Mail и убедитесь, что всё работает правильно. С возможными проблемами с почтой поможет статья: Устранение неполадок в работе компонента Database Mail

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

USE MSDB Go GRANT EXECUTE ON msdb.dbo.sp_send_dbmail TO public GO

Для того, чтобы задействовать очереди событий для интересующих нас баз данных, нужно включить компонент Service Broker. Как это сделать для базы AdventureWorks показано в следующем сценарии:

USE master GO ALTER DATABASE [AdventureWorks] SET ENABLE_BROKER WITH NO_WAIT -- с базой должен работать брокер SELECT name, is_broker_enabled FROM sys.databases -- проверка, что брокер включён GO

Если в ответ на это действие вы получили сообщение об ошибке: The SQL Server Service Broker for the current database is not enabled Можно включить брокера принудительно, например, так:

ALTER DATABASE [AdventureWorks]SET NEW_BROKER WITH ROLLBACK IMMEDIATE GO ALTER DATABASE [AdventureWorks] SET ENABLE_BROKER GO

 

Создание очереди, службы, сообщения и маршрута

В качестве места базирования наших очередей и сообщений мы выберем системную базу данных tempdb. Она есть на любом сервере и Service Broker для неё включён по умолчанию. Первым делом мы заготавливаем процедуру, которая будет вызываться при регистрации интересующего нас события:

USE tempdb GO CREATE PROCEDURE [p_Notify] AS DECLARE @subject nvarchar(128), @body nvarchar(MAX) DECLARE @NotificationStore TABLE (message_body varbinary(MAX)); -- извлекаем сообщения из очереди и помещаем их в табличную переменную RECEIVE TOP (1) message_body FROM tempdb..DEADLOCK_Queue INTO @NotificationStore -- собираем тему и содержимое электронного письма SELECT TOP (1) @body = CAST(message_body AS nvarchar(MAX)) FROM @NotificationStore SELECT @subject = CAST(CURRENT_TIMESTAMP AS varchar) + ' Случился DEADLOCK' IF @body IS NOT NULL EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ПОЧТОВЫЙ ПРОФИЛЬ' -- профиль по умолчанию и не скрытый ,@recipients = 'АДРЕСАТ@ХОСТ.ru' -- адресат ,@subject = @subject ,@body = @body GO -- создаём очередь CREATE QUEUE DEADLOCK_Queue WITH STATUS = ON ,RETENTION = OFF ,ACTIVATION ( PROCEDURE_NAME = tempdb..p_Notify, MAX_QUEUE_READERS = 1, EXECUTE AS 'dbo' ) GO -- создаём службу CREATE SERVICE DEADLOCK_Notify ON QUEUE DEADLOCK_Queue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) GO -- определяем локальную маршрутизацию CREATE ROUTE DEADLOCK_Route WITH SERVICE_NAME = 'DEADLOCK_Notify', ADDRESS = 'LOCAL'; GO -- создаём уведомление уровня сервера для событий появления графа тупиковой блокировки CREATE EVENT NOTIFICATION DEADLOCK_Event ON SERVER FOR DEADLOCK_GRAPH -- если нужны все виды сообщений о блокировках, используйте TRC_LOCKS TO SERVICE 'DEADLOCK_Notify', 'current database' -- проверка SELECT * FROM sys.server_event_notifications WHERE name = 'DEADLOCK_Event'; SELECT * FROM sys.server_events GO

После того, как представленный выше сценарий будет успешно применён на тестовом сервере, будет создано всё необходимое для получения почтовых сообщений. В теле письма будет правильный XML, у которого в теге <TextData> лежит стандартный граф взаимоблокировки, который открывается тэгом <deadlock-list>. Если его вырезать и сохранить в файле с расширением xdl, то при открытии такого файла в приложении SQL Server Management Studio, вы увидите привычную схему блокировки, обычно получаемую с помощью SQL Server Profiler.

Удаление созданных для тестовых целей объектов

Удалить следы наших сценариев в базе данных tempdb помогут следующие команды:

-- DROP PROCEDURE p_Notify -- DROP EVENT NOTIFICATION DEADLOCK_Event ON SERVER -- DROP ROUTE DEADLOCK_Route -- DROP SERVICE DEADLOCK_Notify -- DROP QUEUE DEADLOCK_Queue -- ALTER DATABASE [AdventureWorks] SET DISABLE_BROKER WITH NO_WAIT

Бонус

Если Вы хотите, чтобы стандартный файл взаимоблокировки приходил в письме и в качестве вложения, измените процедуру следующим образом:

USE tempdb GO ALTER PROCEDURE [p_Notify] AS DECLARE @subject nvarchar(255), @x XML, @body NVARCHAR(MAX), @query NVARCHAR(MAX), @filename nvarchar(30) DECLARE @NotificationStore TABLE (message_body varbinary(MAX)); -- извлекаем сообщения из очереди и помещаем их в табличную переменную RECEIVE TOP (1) message_body FROM tempdb..DEADLOCK_Queue INTO @NotificationStore -- собираем тему и содержимое электронного письма SELECT TOP (1) @x = CAST(message_body AS XML) FROM @NotificationStore SELECT @body = CAST(@x AS nvarchar(MAX)) SELECT @x = @x.query('/EVENT_INSTANCE/TextData/deadlock-list') SELECT @subject = CONVERT(nvarchar(19), CURRENT_TIMESTAMP, 126) + '_DEADLOCK' SELECT @filename = REPLACE(@subject,':','')+ '.xdl' IF @body IS NOT NULL BEGIN SELECT @query = N'SET NOCOUNT ON SELECT ' + N'''' + REPLACE(CAST(@x AS nvarchar(MAX)),'''','') + N'''' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ПОЧТОВЫЙ ПРОФИЛЬ' -- профиль по умолчанию и не скрытый ,@recipients = 'АДРЕСАТ@ХОСТ.ru' -- адресат ,@subject = @subject ,@body = @body ,@query = @query ,@execute_query_database = 'tempdb' ,@query_attachment_filename = @filename ,@attach_query_result_as_file = 1 ,@query_result_header = 0 ,@query_result_width = 32767 END GO

 

В тему:

Published Tue, Sep 16 2008 22:50 by gladchenko
Filed under: ,

Comments

# re: Tips for DBA: Deadlock Event Notifications

Ещё один метод "Proactive Deadlock Notifications": blogs.lessthandot.com/.../DataMgmt

Тут используется DBCC TRACEON (3605,1204,1222,-1)

Monday, November 17, 2008 9:17 AM by gladchenko

Leave a Comment

(required) 
(required) 
(optional)
(required) 
If you can't read this number refresh your screen
Enter the numbers above: