Logging deadlocks in sql server Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 80 of sequel server
tutorial in this video we'll discuss how
to write the deadlock information to the
sequel server error log this is
continuation to part 79 so please watch
part 79 before proceeding by default the
deadlock information is not written to
the error log when deadlocks occur
sequel server chooses one of the
transactions as the deadlock victim and
rules it back there are several ways in
sequel server to track down the queries
that are causing deadlocks one of the
options is to use the sequence of a
trace flag 12:22 to write the deadlock
information to the sequence of an error
log for that to happen we must first
turn on that trace flag in sequel server
there are two options to turn on trades
flags we can do that either way using
sequence of a start up parameters or by
using sequence of a dbcc commands as you
can see here these trades flags can be
set at two levels they can be set either
at the global level or at a specific
session level the first command here is
turning on the trace flag 12:22 at the
global level
so the second parameter minus one
indicates that the trace flag must be
set at the global level if we omit this
minus one parameter then the trades flag
will be set only at the session level to
check the status of the trace flag use
trade status and to turn it off use
trace off again if you specify this
second parameter minus one it will be
turned off at the global level if you
omit that it will be turned off at this
action level let's look at this in
action so first let's go ahead and turn
it on at the session level to check the
status let's use trade status notice
that status is set to 1 meaning 1222 is
turned on but it's only turned on at the
session level let's turn it off and now
let's enable it at the global level and
to enable at the global level we're
using that second parameter minus 1 now
let's check the status notice that it's
enabled at the global level now at this
point if there are any dead locks
about those deadlocks will be captured
in the error log and then we can look at
that error log and see what transactions
are causing those deadlocks so let's go
ahead now and write some code to cause
deadlocks for this I'm going to use the
code that we worked with in part 78 so
this code is going to create two tables
table a and table B I have also created
two stored procedures sp transaction 1
and sp transaction 2 both of these
stored procedures are trying to update
both the table stimulation table B the
only difference is you know the first
toad procedure is updating table a first
and then table B and the second stored
procedure is doing it in the reverse
order it's updating table B first and
then table a and in between both the
update statements there is a delay of 5
seconds so at this point if we execute
these two stored procedures from two
different sessions that is from two
different connections then you know they
are going to cause a deadlock let's look
at that action now so let's first
execute store procedure one and then
simultaneously stored procedure two so
after a few seconds one transaction
should succeed and the other one should
roll back and this transaction 2 here is
made the deadlock victim and look at
that we get that 12:05 error so the
information about this deadlock should
now be captured in the error log so the
obvious next question is how to read the
error log for that we are going to make
use of the system stored procedure SP
underscore read error log so let's go
ahead and read that execute SP
underscore read error log
and notice that from here the
information about the deadlock starts in
our next video we'll discuss how to read
and understand this deadlock information
that we have in the error log thank you
for listening and have a great day