🔒 DeadlockCollector

-- Job manuell starten 
EXEC msdb.dbo.sp_start_job N'DeadlockCollector'; 
 
-- Ergebnis prüfen 
SELECT TOP 20 
Event_DateTime, 
DatabaseName_1, 
SQLText_1, 
SQLText_2, 
LockMode_1, 
LockMode_2, 
AffectedProcesses 
FROM DeadlockCollector.dbo.vwvDeadlockInfos 
ORDER BY Event_DateTime DESC;

-- Deadlocks älter als ein bestimmtes Datum löschen 
DECLARE @removed int; 
 
EXEC DeadlockCollector.dbo.deleteDeadLock 
@DeleteOlderAs = '2026-01-01', 
@DeadlocksRemoved = @removed OUTPUT; 
 
SELECT @removed AS [Gelöschte Einträge];

-- Deadlocks aus gespeicherter XEvent-Datei importieren (ab SQL Server 2012) 
DECLARE @collected int; 
 
EXEC DeadlockCollector.dbo.InsertDeadLock 
@XESource = N'\\server\share\system_health*.xel', 
@DeadlocksCollected = @collected OUTPUT; 
 
SELECT @collected AS [Importierte Deadlocks];

SELECT 
Date, 
Day, 
NumOccurances, 
AffectedProcesses, 
Victims 
FROM DeadlockCollector.dbo.vwvDeadLockByDay 
ORDER BY Date DESC;

SELECT 
Date, 
[Hour], 
NumOccurances, 
Sum_AffectedProcesses, 
Sum_Victims 
FROM DeadlockCollector.dbo.vwvDeadLockByDayHour 
WHERE NumOccurances > 0 
ORDER BY Date DESC, [Hour] ASC;

SELECT TOP 20 
NumOccurances, 
AffectedProcesses, 
LEFT(SQLText_1, 120) AS SQLText_1_Snippet, 
LEFT(SQLText_2, 120) AS SQLText_2_Snippet 
FROM DeadlockCollector.dbo.vwvDeadLockCombinationBySQLText 
ORDER BY NumOccurances DESC;

SELECT 
DatabaseName_1, 
DatabaseName_2, 
COUNT(*) AS NumDeadlocks, 
MIN(Event_DateTime) AS FirstSeen, 
MAX(Event_DateTime) AS LastSeen 
FROM DeadlockCollector.dbo.vwvDeadlockInfos 
GROUP BY DatabaseName_1, DatabaseName_2 
ORDER BY NumDeadlocks DESC;

SELECT TOP 10 
Event_DateTime, 
DatabaseName_1, 
LoginName_1, App_Name_1, 
LockMode_1, IsolationLevel_1, 
LEFT(SQLText_1, 200) AS SQLText_1, 
-- Beteiligter Prozess 2 
LoginName_2, 
LockMode_2, 
LEFT(SQLText_2, 200) AS SQLText_2 
FROM DeadlockCollector.dbo.vwvDeadlockInfos 
ORDER BY Event_DateTime DESC;

-- In SSMS: XML-Link anklicken → Deadlock-Diagramm wird gerendert 
SELECT TOP 1 
Event_DateTime, 
Complete_DeadlockGraph, 
Victim_List, 
Resource_List 
FROM DeadlockCollector.dbo.vwvDeadlockInfos 
ORDER BY Event_DateTime DESC;

SELECT 
Date, 
Day, 
NumOccurances, 
AVG(NumOccurances) OVER ( 
ORDER BY Date 
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW 
) AS Avg7Days 
FROM DeadlockCollector.dbo.vwvDeadLockByDay 
WHERE Date >= DATEADD(day, -30, CAST(GETDATE() AS date)) 
ORDER BY Date ASC;

SELECT 
COALESCE(LoginName_1, LoginName_2) AS Login, 
COALESCE(App_Name_1, App_Name_2) AS Application, 
COUNT(*) AS NumDeadlocks 
FROM DeadlockCollector.dbo.vwvDeadlockInfos 
GROUP BY 
COALESCE(LoginName_1, LoginName_2), 
COALESCE(App_Name_1, App_Name_2) 
ORDER BY NumDeadlocks DESC;