Copyright © 2008-2019 Paula DiTallo

Tag Cloud

How do I set a trace in SQL Server?

Think of this as a lightweight alternative to SQL profiler. Under the hood of SQL profiler, there exists SQL Trace  -- which provides a collection of stored procedures to generate trace info. Cut and paste the t-script below to see how it works.

-- Pay attention to what the server settings for traces look like first:

select * from sys.traces


-- you are looking to make sure there isn't already a trace file set somewhere
-- In any case you will need to create a new trace, make sure the @tracefile doesn't exist on the disk yet

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

declare @myTrace_id int

declare @maxsize bigint

set @maxsize =1

exec sp_trace_create @myTrace_id output,2,@MyTracefile ,@maxsize



---  add the result columns you care about

--  if you don't have any other traces set, you'll be set to 1, if not, run the select * sys.traces again to see
-- what got assigned. look up in sys.traces to find the @mytrace_id,
--  in this example, I will assume its 1 for now so that is why you see: @mytrace_id=1

declare @myTrace_id int

set @myTrace_id=1

declare @xon bit

set @xon=1

declare @current int

set @current =1

while(@current  <10)   -- 10 times is just an arbitrary number of times i might log something
                                               -- pick whatever you want

      -- here is where you figure out what events you want to log to the file.  
      -- Go here to decide:

      -- Try it out for now, using #14 -- that's probably not what you want, but get it to work first.

      exec sp_trace_setevent @myTrace_id,14, @current,@xon

      set @current=@current+1

-- later you'll want to look at it

declare @myTrace_id int

set @myTrace_id=1

exec sp_trace_setstatus  @myTrace_id,1

-- see the traced event

select yourlogin, dbinstancename,* from ::fn_trace_gettable(N'myTraceFile.trc',default)


-- Once you're done, you'll need to cleanup
-- stop the trace and delete the file

declare @myTrace_id int

set @myTrace_id=1

exec sp_trace_setstatus @myTrace_id,0

exec sp_trace_setstatus @myTrace_id,2  -- delete def from server

Friday, May 15, 2015 7:31 AM


No comments posted yet.

Post A Comment