데드락 경고 시스템 감지 방법 DeadLock

USE DBA
GO

--추적 트레이스를 시작
DBCC TRACEON(1222, 1204, -1)
GO

--교착 상태를 캡쳐할 테이블 생성
CREATE TABLE DeadlockCheck ( DeadlockInfo XML, CheckTime DATETIME);
GO

--프로시저 생성
CREATE PROCEDURE [dbo].[Up_DeadLockCheck_Ins]
AS
SET NOCOUNT ON

INSERT INTO [dbo].[DeadlockCheck] (DeadlockInfo, CheckTime)
SELECT CAST(REPLACE(REPLACE(TBLX.XEvent.value('(data/value)[1]', 'varchar(max)'), '<victim-list>', '<deadlock><victim-list>'),
    '<process-list>', '</victim-list><process-list>') as xml) as DeadlockInfo
    , GETDATE() CheckTime
FROM (
    select CAST(target_data as xml) as TargetData
    from sys.dm_xe_session_targets st join sys.dm_xe_sessions s
        on s.address = st.event_session_address
    where name = 'system_health') as Data
        cross apply TargetData.nodes('//RingBufferTarget/event') as TBLX(XEvent)
WHERE TBLX.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'


--데드락 잡
use msdb
go

exec sp_add_job @job_name = '[경고] 데드락 발생'
exec sp_add_jobstep @job_name = '[경고] 데드락 발생'
    , @step_id = 1
    , @step_name = '데드락 발생'
    , @subsystem = 'TSQL'
    , @command = 'EXEC dbo.Up_DeadLockCheck_Ins'
    , @database_name = 'DBA'

exec sp_add_jobserver @job_name = '[경고] 데드락 발생', @server_name = N'(local)'
GO

--데드락 경고
EXEC sp_add_alert
    @name = '[경고] 데드락발생경고'
    , @message_id = 0
    , @severity = 0
    , @enabled = 1
    , @delay_between_responses = 0
    , @include_event_description_in = 0
    , @category_name = N'[Uncategorized]'
    , @performance_condition = N'SQLServer:Locks|Number of Deadlocks/sec|_Total|>|0'
    , @job_name = '[경고] 데드락 발생'