November 30, 2017 at 8:52 am
Hello Folks,
I am looking out for a T-SQL script that would send out an email alert as soon as it gets hold of the deadlock information and send out the .xml file as an attachment. I know this has been done in the past and I don't need to re-invent the wheel. However, I haven't found anything closest to what I am looking for
E.g alert would be
Subject line would be "Deadlock occured on X server"
Email body would just say- PFA.
Attachment: .xml file
I would appreciate if someone can provide me this information or at least give me a direction.
November 30, 2017 at 9:02 am
ALTER DATABASE InstanceMonitor SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE InstanceMonitor SET ENABLE_BROKER
GO
ALTER DATABASE InstanceMonitor SET MULTI_USER
GO
USE InstanceMonitor
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'InstanceMonitor' ;
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
EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1
GO
This will be a starting point for you, will need a tweak to the job or alert to do a mail task with the inserted XML as an attachment
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy