SQL Server Quick Tasks – Setup deadlock event trace with T-SQL

Some folks may not know that there is an easy way to setup and monitor via server side trace from T-SQL, although this functionality has been in SQL Server for many years. In SQL Server 2012 and above there is a newer method of monitoring for events known as XEvents – http://technet.microsoft.com/en-us/library/bb630354(v=SQL.105).aspx, however very recently I had to setup a little trace with deadlock graph events to analyze occasional deadlock occurrences on SQL Server 2008 where xEvents , although exist are rather limited.

Therefore I decided to go “old school” and setup a little trace to get deadlock information. Doing it through SQL Server Profiler GUI is easy, however so is through T-SQL.   In this trace I will only capture deadlock events such as Lock: Deadlock, Lock: DeadlockGraph, Lock: DeadlockChain.

SQL Trace provides a set of stored procedures to create traces on an instance of the SQL Server Database Engine. These system stored procedures can be used from within user’s own applications to create traces manually, and allows to write custom applications specific to their needs.

First lets take a, look at current server side traces present on my instance:

select * from sys.traces 

As to be expected I have SQL Server default trace running under id of 1:




Now lets then create our own trace here:

declare @tracefile nvarchar(500) set @tracefile=N'c:\temp\deadlock_trace.trc'

declare @trace_id int

declare @maxsize bigint

set @maxsize =100

exec sp_trace_create @trace_id output,2,@tracefile ,@maxsize 



--- add the events of insterest to be traced, and add the result columns of interest

--  Note: look up in sys.traces to find the @trace_id, here assuming this is the first trace in the server, therefor @trace_id=1

declare @trace_id int

set @trace_id=2

declare @on bit

set @on=1

declare @current_num int

set @current_num =1

while(@current_num <65)


      --add events to be traced, id 59 is the Deadlock Chain event, 148 deadlock graph event,25 Lock Deadlock  you add other events per your own requirements, the event id can be found @ BOL http://msdn.microsoft.com/en-us/library/ms186265.aspx

      exec sp_trace_setevent @trace_id,59, @current_num,@on
      exec sp_trace_setevent @trace_id,148, @current_num,@on
      exec sp_trace_setevent @trace_id,25, @current_num,@on

      set @current_num=@current_num+1



Ok, above we started trace with max size of 100 MB, monitoring just deadlock events, althrough we of course could use number of other events as listed in BOL for sp_trace_setevent – http://msdn.microsoft.com/en-us/library/ms186265.aspx .

Now running query on sys.traces returns both default and our trace here:


Well, that’s all great, but how do I turn off this trace? Simple. From above we know that our trace id is 2. Therefore couple of calls below will turn off and deactivate this trace via sp_trace_setstatus system stored procedure – http://msdn.microsoft.com/en-us/library/ms176034.aspx

declare @trace_id int

set @trace_id=2
exec sp_trace_setstatus @trace_id,0 
exec sp_trace_setstatus @trace_id,2 


Now we are back to default trace only running again, as to be expected.  Hope this little “old school” tip helps someone and plan to blog deeper on new xEvent functionality with SQL Server 2012 and 2014 soon.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s