/* Configuring Deadlock Alert on PRD servers.*/.
/* Step 1 -- Enable Trace Flag 1222 to 1 to capture deadlock information in log files
Note: Trace flag will be reset after every SQL server reset so we need to enable flag after every SQL services restart or configure at startup parameter
*/
/* Step 2 -- Creating an Deadlock Alert and Trigger the Deadlock Job
USE [msdb]
GO
/****** Object: Alert [DeadLock Alert] Script Date: 05/16/2012 23:28:16 ******/
EXEC msdb.dbo.sp_add_alert @name=N'DeadLock Alert',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@performance_condition=N'SQLServer:Locks|Number of Deadlocks/sec|_Total|>|0',
@job_id=N'8a31ce05-3880-4fa4-a020-0263135d0129'
--Job ID needs to be replaced
GO
*/
/* -- Step 3.Create an new job and place an below Query in Job Step*/
/*--Create an temporary table to capture the data from the log file */
IF OBJECT_ID('tempdb.dbo.ErrorLog') IS Not Null
BEGIN
DROP TABLE tempdb.dbo.ErrorLog
END
CREATE TABLE tempdb.dbo.ErrorLog
(Id int IDENTITY (1, 1) NOT NULL,
logdate DATETIME, procInfo VARCHAR(10),
ERRORLOG VARCHAR(MAX))
/*--Insert the data into errorlog table from logs using SP_readerrorlog.*/
INSERT INTO tempdb.dbo.ErrorLog
EXEC master.dbo.sp_readerrorlog
/*--Query to execute the alert using DB mail */
BEGIN
DECLARE @servername nvarchar(150)
SET @servername = @@servername
DECLARE @mysubject nvarchar(200)
SET @mysubject = 'Deadlock event notification on server '+@servername+'.'
/* --Use this query in SQl 2000
--IF EXISTS(select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog
where Id >=(select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like '%Deadlock encountered%'
order by Id DESC))
--BEGIN
-- EXEC msdb.dbo.sp_send_dbmail
-- @profile_name ='DB Mail Profile',
-- @recipients='Deadlock@Gmail.com;Perfteam@Gmail.com ',
-- @subject = @mysubject,
-- @body = 'Deadlock has occurred. View attachment to see the deadlock info',
-- @query = 'select logdate, procInfo, ERRORLOG from tempdb.dbo.ErrorLog where Id >= (select TOP 1 Id from tempdb.dbo.ErrorLog WHERE ERRORLOG Like ''%Deadlock encountered%'' order by Id DESC)
-- and convert(varchar,logdate,112)=convert(varchar,getutcdate(),112)',
-- @query_result_width = 600,
-- @attach_query_result_as_file = 1,
-- @importance='HIGH'
*/
/* Below query is applicable for SQL 2005 higher versions*/
IF EXISTS(
SELECT TOP 1 B.PROCINFO FROM TEMPDB.DBO.ERRORLOG B ,
(SELECT TOP 1 A.LOGDATE, A.PROCINFO FROM TEMPDB.DBO.ERRORLOG A
WHERE A.ERRORLOG LIKE '%Deadlock%' and A.ERRORLOG not like '%EXEC dbo.proc_MSS%'
--AND CONVERT(VARCHAR,A.LOGDATE,112)=CONVERT(VARCHAR,GETUTCDATE(),112)
ORDER BY A.PROCINFO DESC
)A WHERE B.LOGDATE=A.LOGDATE AND B.PROCINFO=A.PROCINFO
)
/* Above query is used to find the deadlock in the Errolog table if deadlock exists then below DB Mail Script will be execute with captured deadlock information and will send the mail*/
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='DB Mail Profile',
@recipients='Deadlock@Gmail.com',
@copy_recipients='Perfteam@slk-soft.com',
@subject = @mysubject,
@body = 'Deadlock has occurred. View attachment to see the deadlock info',
/*Actual Query to get the deadlock information and send the information via mail*/
@query = 'SELECT B.* FROM TEMPDB.DBO.ERRORLOG B ,
(SELECT TOP 1 A.LOGDATE, A.PROCINFO FROM TEMPDB.DBO.ERRORLOG A
WHERE A.ERRORLOG LIKE ''%Deadlock%''
--AND CONVERT(VARCHAR,A.LOGDATE,112)=CONVERT(VARCHAR,GETUTCDATE(),112)
ORDER BY A.PROCINFO DESC
)A
WHERE B.LOGDATE=A.LOGDATE AND B.PROCINFO=A.PROCINFO ORDER BY 1',
@query_result_width = 600,
@attach_query_result_as_file = 1,
@importance='HIGH'
END
END
DROP TABLE tempdb.dbo.ErrorLog
