Discussion :

[2nd Floor]

Bonjour à tous,
J'aurais besoin de vos lumières pour créer un trigger d'alerte (avec email ou non) lors de la présence d'un deadlock sur ma base SQL SERVER 2008 R2.

J'ai lu tellement de choses que je préfererais avoir l'avis de professionnels comme les experts de Developpez.com ou autre membre confirmé.

Merci beaucoup,
Gwenael

[zinzineti]

Sous SQL Server 2008 et + ; tu peux utiliser XEvents pour déclencher ton alerte.
La requête ci-dessous renvoie un fichier XML montrant les requêtes responsables des deadlocks. Pour plus d'infos

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
DECLARE @xml xml 
SELECT @xml = CAST(st.target_data as xml) 
FROM sys.dm_xe_session_targets st 
INNER JOIN sys.dm_xe_sessions s on s.address = st.event_session_address 
INNER JOIN sys.server_event_sessions es on s.name = es.name 
WHERE s.name = 'system_health' AND st.target_name = 'ring_buffer' 
-- and convert(varchar(8),s.create_time,112) = '20120422' -- cibler une date particulière 
SELECT @xml.query('/RingBufferTarget/event [@name="xml_deadlock_report"]')

[mikedavem]

Une autre voix :

Utiliser les Events SQL Server et l'événement DEADLOCK_GRAPH

>> http://msdn.microsoft.com/en-us/library/ms186385.aspx

Les Xevents sont un bon moyen également de retrouver cela mais attention car avec 2008 le XML généré par les Xevents n'est pas généré correctement. De plus pour capturer les deadlocks via Xevent je conseille de ne pas utiliser la session 'system_health' qui ne permet pas forcément de garder les événements assez longtemps pour les capturer de façon durable.

++

[2nd Floor]

Merci beaucoup pour vos réponses, je vais regarder tout cela.
Bon week-end à tous

[elsuket]

Bonjour,

Si l'on souhaite simplement être alerté lors de l’occurrence d'un verrou mortel (c'est à dire sans les détails des ressources impliquées dans celui-ci), on peut tout simplement mettre en place une alerte de l'Agent SQL Server :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
USE [msdb]
GO
 
-- Ajout d'un opérateur
-- En fait, c'est un pointeur sur une addresse e-mail
EXEC msdb.dbo.sp_add_operator
	@name = N'Deadlock_Operator'
	, @enabled = 1
	, @pager_days = 0
	, @email_address = N'deadlock_operator@deadlock.com'
GO
 
-- Ajout d'une alerte sur verrou mortel
EXEC msdb.dbo.sp_add_alert
	@name = N'Deadlock'
	, @message_id = 1205
	, @severity = 0
	, @enabled = 1
	, @delay_between_responses = 0
	, @include_event_description_in = 1
GO
 
-- Attachement de l'opérateur à l'alerte
EXEC msdb.dbo.sp_add_notification
	@alert_name = N'Deadlock'
	, @operator_name = N'Deadlock_Operator'
	, @notification_method = 1
GO

Si la fonctionnalité de Messagerie de Bases de Données est activée, ce qui semble être le cas, c'est du gâteau.

On peut aussi créer une notification d’évènements, à l'aide de l'événement DEADLOCK_GRAPH, mais la mise en route de Service Broker et la création de la queue, du service et de la notification me semble un peu lourde pour ce faire.

Si on souhaite en plus avoir les détails, on peut créer une trace SQL Trace côté serveur qui capture tous les deadlocks, et les enregistre dans un fichier de trace.
On peut tout à fait se limiter à exécuter simplement le script généré avec SQL Server Profiler directement, sans utiliser un travail de l'Agent SQL Server qui enregistrera ceux-ci dans une table à intervalles réguliers. Ainsi lors de la réception de l'alerte, il suffit d'ouvrir le fichier de trace avec SQL Profiler, et le tour est joué.

@++