Deadlock Alerts via Email
Published: 13th October 2013
In the second episode of Sheldon Cooper’s Fun with Flags, we’re going to be looking for deadlocks.

There are numerous ways of tracking deadlocks within SQL Server but as we rarely know when they’re going to occur, don’t want traces running all day, and want to react quickly, the best method I’ve found is to have them sent to me in an email each time they appear.

This is by far my favourite method as it’s simple, effective, and fast. It also includes everything you need to know about your deadlocks and, although it looks complex on first viewing, isn’t too hard to decipher.

All you need is an Alert and a couple of trace flags.

So let’s get started…

First things first, we need our trace flags. Now I know that people tend to stick to one or other of these, but I like to cover all bases by enabling both 1204 and 1222.

Therefore let’s enable those now:

dbcc traceon(1204, -1)
go

dbcc traceon(1222, -1)
go


With those in place we know our SQL Server is now logging all deadlock information in the error log. Therefore we need something to get it out so let’s set up a job that will return our deadlock information to us.

Here’s the code we’ll be using in order to obtain the data from our logs…

if object_id('tempdb..##error') is not null drop table ##error
go

create table ##error
(
id int identity(1, 1),
logDate datetime,
processInfo varchar(20),
errorText nvarchar(max)
)

insert into ##error
exec master.dbo.sp_readErrorLog

select logDate, processInfo, errorText
from ##error
where id >=
(
  
select max(id)
  
from ##error
  
where errorText like '%deadlock encountered%'
)

declare @subject varchar(250)
select @subject = 'Deadlock reported on ' + @@servername

EXEC msdb.dbo.sp_send_dbmail
      
@profile_name = 'My Mail Server',
      
@recipients=myEmail@myEmail.com',
      
@subject = @subject,
      
@body = 'A deadlock has been recorded.  Further information can be found in the attached file.',
      
@query = 'select logDate, processInfo, errorText
from ##error
where id >=
(
select max(id)
from ##error
where errorText like ''%deadlock encountered%''
)'
,
      
@query_result_width = 600,
      
@attach_query_result_as_file = 1

drop table ##error


Now we’ll force a deadlock…

In one management studio window, run the following code:

create table ##temp1 (id int)
create table ##temp2 (id int)

insert ##temp1 values(1), (2), (3)

insert ##temp2 values(1), (2), (3)

begin transaction
   update
##temp1 set id = 4 where id = 1

  
waitfor delay '00:00:20'

  
update ##temp2 set id = 4 where id = 1
commit transaction

drop table
##temp1
drop table ##temp2


Whilst this is running (you have around 20 seconds), run the following code in a different management studio window:

begin transaction
   update
##temp2set id = 4 where id = 1

  
waitfor delay '00:00:20'

  
update ##temp1 set id = 4 where id = 1
commit transaction


After the 20 seconds is up, you’ll see a deadlock has occurred. Now, running your deadlock code should mean that you receive an email with attachment.

Open the attachment and without too much effort you can pick out the key components:

Here are the spids that are involved…

Just below each spid you can see the exact code that was running on that spid.

Also at the end of the file you can see what caused the deadlock… in this case both trying to obtain exclusive locks…

So… we now have our deadlocks and associated email… how do we get notified each time one happens? Well we use an Alert.

Firstly we need to create a scheduled job and insert our code to send us deadlock information. In my case I’ve created a job called “Deadlock Alert Job”…

Now we need to create a new alert…

Set up your alert to fire on a Performance Condition, and select Deadlocks as follows…

Then, in Responses, have the Alert call your new SQL Job…

Now, whenever SQL Server detects a deadlock your job will be fired and you will receive an email with all the information you require in order to track down your culprits and make the appropriate fixes.
Comments:
Kirbu Chan
13/01/2015 10:28:00
Very useful. I like the way you explain everything it is very detailed and easy to understand. I'm currently using this to our Production Server to Alert us in case we encounter Deadlock and someone in the Admin is needed to be notify in case deadlock occurs:)
Ananda M
14/03/2017 10:19:00
Hi,

Deadlock script working fine.. but attachment text file deadlock involvements SPID not avilable also blank file.
Pl. help.
nathan
29/03/2017 13:23:00
If sql services restart happened then the dead lock information cannot be logged in error log untill we execute below dbcc again right .Kindly correct if i am wrong.
dbcc traceon(1204, -1)
go

dbcc traceon(1222, -1)
go
NB: Comments will only appear once they have been moderated.