September 10, 2008 at 12:25 am
I've been trying to setup an alert for deadlocks based upon Microsoft documentation and other articles, but have been unable to get my email messages to include the server name. I'm still fairly new to WMI so I'm guessing I am missing something simple.
This is the message in my email generated by an Alert.
SQL Server Alert System: 'Respond to DEADLOCK_GRAPH' occurred on \\
I created an alert and a SQL job to capture the deadlock information, but as you see above the server name is missing.
Do you have any good links to articles/discussions explaining all of the various SQL Server WMI alerts that can be generated?
Thanks, Dave
September 10, 2008 at 1:05 am
Dave
unfortunately you don't post the syntax for you're job. But the way I would go is executing the job in response to the alert and in the jobstep run sp_send_dbmail.
[font="Verdana"]Markus Bohse[/font]
September 10, 2008 at 8:11 am
Here is the syntax.
-- Not sure if 1204 is necessary in SQL 2005. I have read conflicting articles, but thought I would see for myself
DBCC TRACEON(1204, 1222, 3605, -1)
-- Add the Alert
-- For the default instance use MSSQLSERVER.
-- For a named instance replace MSSQLSERVER with the instance name.
Declare @NameSpace NVarchar(200)
Select @NameSpace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER';
EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH',
@wmi_namespace=@NameSpace,
@wmi_query='SELECT * FROM DEADLOCK_GRAPH',
@job_name='Capture Deadlock Graph'
Set Quoted_Identifier OFF
IF EXISTS(select * from msdb..sysjobs where name = 'Capture Deadlock Graph')
Begin
EXECUTE msdb.dbo.sp_delete_job @job_name = 'Capture Deadlock Graph'
End
-- 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',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'sa'
-- Add a jobstep that inserts the current time and the deadlock graph into the DeadlockEvents table.
Declare @CommandNVarchar(1000)
Set @Command = "INSERT INTO DeadlockEvents (AlertTime, DeadlockGraph)
VALUES (getdate(), N''$(ESCAPE_SQUOTE(WMI(TextData))))"
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=@Command,
@database_name=N'DBA_HOME'
-- 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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply