Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

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)

dbcc traceon(1222, -1)

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

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',
@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
##temp1 set id = 4 where id = 1

waitfor delay '00:00:20'

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

drop table
drop table ##temp2

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

begin transaction
##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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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


© Copyright 2020 SQLTraining Ltd.