Deadlock Notifications in SQL Server 2005

• sqldba-294117

  Hall of Fame

  Points: 3486

  More actions

  September 29, 2011 at 4:12 pm

  #1388647

  I have made minor change to that code as shown below & shorten with few lines.

  Create this store proc and schedule in SQL Agent Job for 10mins. it will only email if there is deadlock.

  sp_send_dbmail as limitation that local variable cannot be pass within @query, but global variable helps & dropping at the end.

  CREATE PROC [dbo].[usp_DeadlockNotification]

  AS

  CREATE TABLE ##ErrorLog

  (

  LogDate DATETIME NOT NULL,

  ProcessInfo VARCHAR(15),

  text VARCHAR(15)

  )

  DECLARE @StartDate DATETIME,@EndDate DATETIME

  DECLARE @Minutes INT

  SET @Minutes = 10

  SELECT @EndDate = GETDATE()

  SELECT @StartDate = DATEADD(mi, -@Minutes, @EndDate)

  INSERT INTO ##Errorlog

  EXEC master.dbo.xp_readerrorlog 0, 1,'deadlock-list',NULL, @StartDate, @EndDate, N'desc'

  Declare @text varchar(15)

  SET @text = (select 1 from ##Errorlog where text = 'deadlock-list' )

  print @text

  select * from ##Errorlog

  IF (@text = 1)

  EXEC msdb.dbo.sp_send_dbmail

  @profile_name = SQL DBA',

  @recipients = 'SQLDBA@abc.com',

  @subject = 'Deadlock Encounter',

  @attach_query_result_as_file = 1,

  @query = 'BEGIN

  select * from ##Errorlog

  END'

  ELSE

  SELECT 'No deadlock found!'

  DROP Table ##Errorlog