February 27, 2012 at 7:26 am
Hi All
In to process of creating a mail alert for deadlocks, I have found this WMI alerting script
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'tempdb' ;
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
But the job is failing with
Unable to start execution of step 1 (reason: Variable WMI(TextData) not found). The step failed.
I've had a look here[/url] but no luck, I have enabled replace tokens and restarted the agent but still nothing.
Any ideas?
February 27, 2012 at 7:48 am
believe I have resolved it
i created a deadlock and it didnt fire the alert
and i was still trying to fire the same deadlock alert, but I had to create a new deadlock
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply