DESCRIPTION
This alert will detect and show deadlocks in SQL Server instance.
ALERT DEFINITION
To create this alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert similar to below.
Note: When DPA executes this alert, it will substitute the proper data for the keyword #DBID# (database ID internal to DPA).
Alert Name: Alert DeadLock
Execution Interval: 3 MInutes
SQL Statement:
SELECT char(13) + char(10) +
'URL: SRVEFEVMDPA:8123/.../deadlockDetails.iwc' + CONVERT(VARCHAR, DB_ID) + '&dl_id=' + CONVERT(VARCHAR, ID) + char(13) + char(10)
, 1
FROM (
SELECT #DBID# AS DB_ID, dl.*, dlo.name
FROM dpa_efecty.dbo.CON_DEADLOCK_#DBID# dl
LEFT JOIN dpa_efecty.dbo.CON_DEADLOCK_OBJ_#DBID# dlo ON (dl.ID = dlo.ID)
) dl
WHERE D > DATEADD(MI, -5, getdate())
Execute Against: Repository
Units: N/A
High Threshold: Min 1, Max empty
