CREATE PROCEDURE dbo.usp_DeadlockReport
@StartTime DATETIME = NULL OUTPUT,
@EndTime DATETIME = NULL OUTPUT,
@ExecuteSQL BIT = 1,
@PrintSQL BIT = 0,
@recipients NVARCHAR(4000) = 'diablosupport.facl.fl@corelogic.com' -- 'gvarol@corelogic.com'
AS
SET NOCOUNT ON
SELECT @StartTime = ISNULL(@StartTime, DATEADD(MINUTE, -60, GETDATE())),
@EndTime = ISNULL(@EndTime, GETDATE())
DECLARE @ProfilePath VARCHAR(1000),
@SQL VARCHAR(MAX)
IF OBJECT_ID('TEMPDB..#Deadlock') IS NOT NULL
DROP TABLE #Deadlock
CREATE TABLE #Deadlock (SQL VARCHAR(MAX) NULL,
ReturnVal INT NULL,
MESSAGE VARCHAR(4000) NULL,
StartTime DATETIME NOT NULL,
TextData VARCHAR(MAX) NOT NULL)
SELECT @ProfilePath = ProfilePath
FROM dbo.Servers
WHERE [SQLSERVERNAME] = @@SERVERNAME
;WITH profile01
AS (SELECT [name],
[Size],
CAST([CreateDate] AS DATETIME) AS [CreateDate],
CAST([LastWritten] AS DATETIME) AS [LastWritten],
CAST([LastAccessed] AS DATETIME) AS [LastAccessed],
[Attributes]
FROM FS.GetDirectoryInfo(@ProfilePath, @@SERVICENAME + '-DeadlockTrace-*.trc')
WHERE [IsDirectory] = 0),
profile02
AS (SELECT CASE WHEN @EndTime > MAX([LastWritten]) THEN MAX([LastWritten])
ELSE @EndTime
END AS EndFileDate,
CASE WHEN @StartTime < MIN([CreateDate]) THEN MIN([CreateDate])
ELSE @StartTime
END AS StartFileDate
FROM profile01),
ProfileFilesList
AS (SELECT TOP 999999
[name] AS FileName,
@ProfilePath + [name] AS FullPath,
[Size],
[CreateDate],
[LastWritten],
[LastAccessed],
[Attributes]
FROM profile01 pl
CROSS JOIN profile02 dr
WHERE (dr.EndFileDate BETWEEN pl.[CreateDate] AND pl.LastWritten)
OR (dr.StartFileDate BETWEEN pl.[CreateDate] AND pl.LastWritten)
OR (dr.StartFileDate <= pl.[CreateDate]
AND pl.LastWritten <= dr.EndFileDate)
ORDER BY [CreateDate])
SELECT @SQL = 'INSERT dbo.DeadlockProfile(StartTime, TextData)
' + dbo.StringConcat('SELECT
StartTime,
CAST(TextData AS VARCHAR(MAX)) AS TextData
FROM fn_trace_gettable ( ''' + FullPath
+ ''' , 1) f
WHERE EventClass = 148 -- Deadlock graph
AND TextData IS NOT NULL
AND NOT EXISTS(SELECT * FROM dbo.DeadlockProfile p WHERE p.StartTime = f.StartTime AND p.TextData = CAST(f.TextData AS VARCHAR(MAX)))
AND StartTime BETWEEN ''' + CONVERT(VARCHAR(30), @StartTime, 120) + ''' AND ''' + CONVERT(VARCHAR(30), @EndTime, 120)
+ '''', '
UNION ALL
')
FROM ProfileFilesList
IF @SQL <> ''
BEGIN
IF @PrintSQL = 1
EXEC dbo.PrintLargeText
@SQL
IF @ExecuteSQL = 1
EXEC(@SQL)
END
INSERT #Deadlock
(SQL,
ReturnVal,
MESSAGE,
StartTime,
TextData)
SELECT 'EXEC msdb.dbo.sp_send_dbmail
@recipients = N''' + @recipients + ''',
@subject = N''Deadlock Report ( Open attachment with Microsoft SQL Server Management Studio )'' ,
@body = N''' + REPLACE(TextData, '''', '''''') + ''',
@file_attachments = N''' + FileName + '''
' AS SQL,
af.ReturnVal,
af.MESSAGE,
StartTime,
TextData
FROM dbo.DeadlockProfile dl
CROSS APPLY (SELECT REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(100), GETDATE(), 121), ' ', '_'), '-', ''),
':', ''), '.', '') AS DeadLockName) st
CROSS APPLY (SELECT 'C:\temp\Deadlock_' + st.DeadLockName + '.xdl' AS FileName) fn
OUTER APPLY (SELECT ReturnVal, MESSAGE FROM FS.AppendAllTextToFile (FileName, TextData, 1)) af
WHERE dl.Notified = 0
IF @@ROWCOUNT > 0
BEGIN
SELECT @SQL = dbo.StringConcat(SQL, '
')
FROM #Deadlock
IF @PrintSQL = 1
EXEC dbo.PrintLargeText
@SQL
IF @ExecuteSQL = 1
BEGIN
EXEC(@SQL)
UPDATE dl
SET Notified = 1
FROM dbo.DeadlockProfile dl
INNER JOIN #Deadlock d2 ON d2.StartTime = dl.StartTime
AND d2.TextData = dl.TextData
WHERE dl.Notified = 0
END
END