APSQL

Définir une alerte WMI

L'exemple mis en pratique ci dessous correspond à la capture des informations lorsqu'un verrou mortel (deadlock) se produit sur le serveur.

Cette mise en place va se dérouler en 3 étapes:

      Créer la table cible
      Mettre en place l'alerte
      Tester le dispositif

Etape 1 : Créer la table

C'est la base test qui va contenir cette table de synthèse nommé SuiviEvenements et créé à l'aide du script:

use test;
go
create table SuiviEvenements(
le datetime,
detail xml);
			

Etape 2 : Créer une alerte

La création de l'alerte peux bien sur être réalisée de façon graphique mais également à l'aide de procédures stockées. Ce sont les procédures sp_add_job, sp_add_job_step, sp_add_jobserver et sp_add_alert qui vont être utilisées. Comme les travaux et alertes sont entièrement définies dans la base msdb, ces procédures existent dans la base msdb.

Le script suivant permet donc de définir le travail qui va être associé à l'alerte. C'est pourquoi la définition du travail interviens avant la définitionn de l'alerte avec la procédure sp_add_alerte

La création du travail

EXEC  msdb.dbo.sp_add_job @job_name=N'Prise en compte des verrous', 
    @enabled=1, 
    @description=N'Travail pour la prise en compte des verrous' ;
GO
			

La défintion de l'étape qui se trouve dans ce travail. L'objectif de cette étape est d'alimenter la table de suivi des évéènements.

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'Prise en compte des verrous',
    @step_name=N'Ajouter dans SuiviEvenements',
    @step_id=1, 
    @on_success_action=1, 
    @on_fail_action=2, 
    @subsystem=N'TSQL', 
    @command= N'INSERT INTO SuiviEvenements
                (le, detail)
                VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData)))'')',
    @database_name=N'Test' ;
GO
			

Le nom de la base de données (ici Test) est à adapté en fonction de l'environnement d'exécution.

On remarque également l'utilisation de la macro ESCAPE_SQUOTE et du TOKEN WMI pour obtenir la description de l'évènement WMI sous la forme de chaine de caractère encadrée par de simple apostrophes.

Il faut maintenant spécifié que le serveur cible est bien le serveur local:

EXEC msdb.dbo.sp_add_jobserver @job_name = N'Prise en compte des verrous';
GO
			

Définir l'alerte qui va déclencher l'exécution du travail:

EXEC msdb.dbo.sp_add_alert @name=N'Suivi des verrous mortel', 
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 
    @wmi_query=N'SELECT * FROM DEADLOCK_GRAPH', 
    @job_name='Prise en compte des verrous' ;
			

Pour permettre la bonne exécution des requêtes WMI il est nécessaire de demander à l'agent SQL de remplacer les jetons pour toute les réponses de travaux et d'alertes. Cette configuration s'effectue en se positionnant sur la page système d'alertes dans la le fenêtre des propriétés de l'agent SQL Server et d'activer la case à cocher correpondante comme illustré ci dessous:

Propriétés de SQL Server Agent

Etape 3: Tester le dispositif

2 tables de test, test1 et test2 sont définis sur la base de test par exemple avec le script suivant:

use test;
go
create table test1(c11 int);
create table test2(c21 int);
go
insert into test1(c11) values (1);
insert into test1(c11) values (2);
insert into test1(c11) values (3);

insert into test2(c21) values (1);
insert into test2(c21) values (2);
insert into test2(c21) values (3);

Depuis une 1ere fenetre de rqt le script suivant est exécuté:

use test;
go
begin tran
select * from test1 with(xlock);
			

Depuis la énde fenetre exécutez le script :

use test;
go
begin tran
select * from test2 with(xlock);

select * from test1 with(xlock);
			

Et enfin pour déclencher le verrour mortel, revenir sur la fenetre 1 et exécuter:

select * from test2 with(xlock);