-- Check Database Exists
IF  NOT EXISTS (SELECT * FROM sys.databases WHERE name = N'DBAEventManagement')
	CREATE DATABASE DBAEventManagement ;
GO
 
USE DBAEventManagement ;
GO

-- Create table to hold deadlock graphs
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeadlockEvents]') AND type in (N'U'))
	DROP TABLE [dbo].[DeadlockEvents]
GO

CREATE TABLE [dbo].[DeadlockEvents](
	[Id] [integer] NOT NULL IDENTITY,
	[AlertTime] [datetime2] NOT NULL CONSTRAINT DF_AlertTime DEFAULT SYSDATETIME(),
	[DeadlockGraph] [xml] NULL
)
GO

-- Create Job to service the event notifications
USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
DECLARE @jobId BINARY(16)
SET @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture Deadlock Graph')
	EXEC msdb.dbo.sp_delete_job @job_name = N'Capture Deadlock Graph' , @delete_unused_schedule=1

-- Create the job to process event first
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Capture Deadlock Graph', 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Job for responding to DEADLOCK_GRAPH events', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert graph into DeadlockEvents', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'INSERT INTO DeadlockEvents(DeadlockGraph)
                VALUES (N''$(ESCAPE_SQUOTE(WMI(TextData)))'')', 
		@database_name=N'DBAEventManagement', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
DECLARE @jobId BINARY(16)
SET @ReturnCode = 0
SET @jobId  = (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture Deadlock Graph')

IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to DEADLOCK_GRAPH')
	EXEC msdb.dbo.sp_delete_alert @name=N'Respond to DEADLOCK_GRAPH'

-- Create the alert for the event
EXEC @ReturnCode = msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH', 
		@message_id=0, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@category_name=N'[Uncategorized]', 
		@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 
		@wmi_query=N'SELECT * FROM DEADLOCK_GRAPH', 
		@job_id=@jobId
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: