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 go --- 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) begin --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 end go
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 go
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.