Esempio: Creazione di un avviso di SQL Server Agent tramite il provider WMI per eventi del server

Esempio

Viene innanzitutto creata una tabella nel database AdventureWorks in cui includere l'evento Deadlock Graph. La tabella è costituita da due colonne: la colonna AlertTime contiene la durata di esecuzione dell'avviso, mentre la colonna DeadlockGraph contiene il documento XML che include l'evento Deadlock Graph.

Viene quindi creato l'avviso. Lo script crea innanzitutto il processo eseguito dall'avviso, aggiunge un passaggio del processo al processo e specifica come destinazione del processo l'istanza corrente di SQL Server. Lo script crea quindi l'avviso.

Il passaggio del processo recupera la proprietà TextData dell'istanza dell'evento WMI e inserisce tale valore nella colonna DeadlockGraph della tabella DeadlockEvents. Si noti che SQL Server converte in modo implicito la stringa in formato XML. Poiché utilizza il sottosistema Transact-SQL, il passaggio del processo non specifica un proxy.

L'avviso esegue il processo ogni volta che viene registrato un evento di traccia Deadlock Graph. Per un avviso WMI, SQL Server Agent crea una query di notifica utilizzando lo spazio dei nomi e l'istruzione WQL specificati. Per questo avviso, SQL Server Agent controlla l'istanza predefinita nel computer locale. L'istruzione WQL richiede tutti gli eventi DEADLOCK_GRAPH nell'istanza predefinita. Per modificare l'istanza monitorata dall'avviso, sostituire il nome dell'istanza per MSSQLSERVER in @wmi\_namespace per l'avviso.

USE AdventureWorks ;
GO

IF OBJECT_ID('DeadlockEvents', 'U') IS NOT NULL
BEGIN
    DROP TABLE DeadlockEvents ;
END ;
GO

CREATE TABLE DeadlockEvents
    (AlertTime DATETIME, DeadlockGraph XML) ;
GO
-- Add a job for the alert to run.

EXEC  msdb.dbo.sp_add_job @job_name=N'Capture Deadlock Graph', 
    @enabled=1, 
    @description=N'Job for responding to DEADLOCK_GRAPH events' ;
GO

-- Add a jobstep that inserts the current time and the deadlock graph into
-- the DeadlockEvents table.

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'Capture Deadlock Graph',
    @step_name=N'Insert graph into LogEvents',
    @step_id=1, 
    @on_success_action=1, 
    @on_fail_action=2, 
    @subsystem=N'TSQL', 
    @command= N'INSERT INTO DeadlockEvents
                (AlertTime, DeadlockGraph)
                VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData))))',
    @database_name=N'AdventureWorks' ;
GO

-- Set the job server for the job to the current instance of SQL Server.

EXEC msdb.dbo.sp_add_jobserver @job_name = N'Capture Deadlock Graph' ;
GO

-- Add an alert that responds to all DEADLOCK_GRAPH events for
-- the default instance. To monitor deadlocks for a different instance,
-- change MSSQLSERVER to the name of the instance.

EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH', 
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 
    @wmi_query=N'SELECT * FROM DEADLOCK_GRAPH', 
    @job_name='Capture Deadlock Graph' ;
GO

Test dell'esempio

Per visualizzare l'esecuzione del processo, provocare un deadlock. In SQL Server Management Studio aprire due schede Query SQL e connettere entrambe le query alla stessa istanza. Eseguire lo script seguente in una delle schede delle query. Questo script produce un set di risultati e viene terminato.

USE AdventureWorks ;
GO

BEGIN TRANSACTION ;
GO

SELECT TOP(1) Name FROM Production.Product WITH (XLOCK) ;
GO

Eseguire lo script seguente nella seconda scheda della query. Lo script produce un set di risultati e quindi si blocca, in attesa di acquisire un blocco su Production.Product.

USE AdventureWorks ;
GO

BEGIN TRANSACTION ;
GO

SELECT TOP(1) Name FROM Production.Location WITH (XLOCK) ;
GO

SELECT TOP(1) Name FROM Production.Product WITH (XLOCK) ;
GO

Eseguire lo script seguente nella prima scheda della query. Questo script si blocca, in attesa di acquisire un blocco su Production.Location. Dopo un breve timeout, SQL Server sceglierà questo script o lo script nell'esempio come vittima del deadlock e terminerà la transazione.

SELECT TOP(1) Name FROM Production.Location WITH (XLOCK) ;
GO

Dopo avere provocato il deadlock, attendere alcuni momenti prima che SQL Server Agent attivi l'avviso ed esegua il processo. Esaminare il contenuto della tabella DeadlockEvents eseguendo lo script seguente:

SELECT * FROM DeadlockEvents ;
GO

La colonna DeadlockGraph deve contenere un documento XML indicante tutte le proprietà dell'evento Deadlock Graph.

Vedere anche

Concetti