Deadlock Alerts via Email
Published: Jan 16, 2015
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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron