Vous êtes ici :
Ce script permet de mettre en place une journalisation des deadlocks dans une table de la base msdb, via une notification d’évènements.
Les informations du deadlock graph sont journalisées en XML dans la table msdb..DeadlockEventsXML. La vue msdb..DeadlockGraphs permet de visualiser les informations au format tabulaire. En particulier, le contenu de la colonne « XDL » de cette vue, sauvegardée au format de fichier .XDL permettra de visualiser le rapport graphique du deadlock dans SSMS.
N’oubliez pas de purger la table de temps en temps…
Transact-SQL
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 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 |
/*------------------------------------------------------------------- [SCRIPT] Deadlocks Event Notification Installation Script [DATABASE] msdb [DESCRIPTION] Installe suivi des deadlocks [MAJ PAR] DATAFLY - Arian Papillon [DATEMAJ] 20190202 -------------------------------------------------------------------*/ USE msdb GO -- Configure les objets du service broker IF EXISTS ( SELECT * FROM sys.services WHERE name = 'NotifyDeadlockService' ) DROP SERVICE NotifyDeadlockService GO IF OBJECT_ID('[dbo].[NotifyDeadlockQueue]') IS NOT NULL AND EXISTS ( SELECT * FROM sys.service_queues WHERE name = 'NotifyDeadlockQueue' ) DROP QUEUE [dbo].[NotifyDeadlockQueue] GO CREATE QUEUE NotifyDeadlockQueue GO CREATE SERVICE NotifyDeadlockService ON QUEUE NotifyDeadlockQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) GO -- Crée la table de journalisation SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DeadlockEventsXML]( [EventNumber] [int] IDENTITY(1,1) NOT NULL, [EventType] [nvarchar](256) NULL, [EventTime] [datetime] NULL, [EventData] [xml] NULL, PRIMARY KEY CLUSTERED ( [EventNumber] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO IF OBJECT_ID(N'[dbo].[LogDeadlockEvents]') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID(N'[dbo].[LogDeadlockEvents]'), N'IsProcedure') = 1 DROP PROCEDURE [dbo].[LogDeadlockEvents] GO -- procédure stockée de journalisation SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[LogDeadlockEvents] AS BEGIN SET NOCOUNT ON DECLARE @message_body XML , @message_type_name NVARCHAR(256) , @dialog UNIQUEIDENTIFIER; WHILE ( 1 = 1 ) BEGIN BEGIN TRANSACTION WAITFOR ( RECEIVE TOP ( 1 ) -- just handle one message at a time @message_type_name = message_type_name, --the type of message received @message_body = message_body, -- the message contents @dialog = conversation_handle -- the identifier of the dialog this message was received on FROM NotifyDeadlockQueue ), TIMEOUT 2000; -- if the queue is empty for two seconds, give up and go away IF ( @@ROWCOUNT = 0 ) BEGIN ROLLBACK TRANSACTION; BREAK; END; IF ( @message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' ) BEGIN PRINT 'Fin du dialogue # ' + CAST(@dialog AS NVARCHAR(40)); END CONVERSATION @dialog; END; ELSE BEGIN INSERT INTO [dbo].[DeadlockEventsXML] ( EventType , EventTime , EventData ) VALUES ( CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS NVARCHAR(256)) , CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS NVARCHAR(MAX)) AS DATETIME) , @message_body ) END COMMIT TRANSACTION END END GO -- Active la file d'attente, ce qui lance la réception des événements ALTER QUEUE [dbo].[NotifyDeadlockQueue] WITH ACTIVATION ( STATUS = ON, PROCEDURE_NAME = [dbo].[LogDeadlockEvents], -- Lance un maximum de 2 instance de la procédure MAX_QUEUE_READERS = 2, -- En tant que l'utilisateur qui a créé la file d'attente EXECUTE AS SELF ) -- Crée la notification IF EXISTS ( SELECT * FROM sys.server_event_notifications WHERE name = 'NotifyDeadlocks' ) DROP EVENT NOTIFICATION NotifyDeadlocks ON SERVER GO CREATE EVENT NOTIFICATION NotifyDeadlocks ON SERVER WITH fan_in FOR DEADLOCK_GRAPH TO SERVICE 'NotifyDeadlockService', 'current database' ; GO -- Vue de visualisation CREATE VIEW DeadlockGraphs AS SELECT [EventNumber] ,[EventType] ,[EventTime] ,[EventData] ,EventData.query('/EVENT_INSTANCE/TextData/deadlock-list') AS XDL FROM [DeadlockEventsXML] |
Table of Contents