mbielanczuk

Creating TRACE on SQL Express server

This is something that can be very helpful if you’re working with SQL Server Express (2005/2008). Other versions have a SQL Server Profiler where you can easily add trace without having to write code.

If we want to list all existing traces we should run select query on sys.traces table.

SELECT * FROM sys.traces

This should give list of all traces on server with their id, status, path to *.trc log file etc…

We’re creating trace with sp_trace_create command:

DECLARE @tid INT
EXEC sp_trace_create
 @traceid = @tid OUTPUT,
 @options = 2,
 @tracefile = N'c:\trace'
SELECT @tid -- this will display our new trace id
GO

After creating new trace we should set events we want to be traced and columns we want to be written when this event occurs. We can do this with sp_trace_setevent procedure.

DECLARE @tid INT, @cid INT
SET @tid = 2     -- our trace id
SET @cid = 1     -- 1 for ON
WHILE(@cid < 65) -- there are 65 columns in result, 
                 -- let's process them all
      BEGIN
	    EXEC sp_trace_setevent @tid, 10, @cid, 1
	    EXEC sp_trace_setevent @tid, 12, @cid, 1
	    EXEC sp_trace_setevent @tid, 14, @cid, 1
	    EXEC sp_trace_setevent @tid, 41, @cid, 1
	    EXEC sp_trace_setevent @tid, 43, @cid, 1
	    SET @cid += 1
      END 
GO

Now we can add some filters to trace, for example we want to trace only inserts on table “Users” in “cms” database. This can be done with sp_trace_setfilter procedure.

DECLARE @tid INT
SET @tid = 2     -- our trace id
EXEC sp_trace_setfilter @tid, 1, 1, 6, N'%insert into %Users%'
EXEC sp_trace_setfilter @tid, 35, 0, 0, N'cms'

Now we can set our trace in running state with command sp_trace_setstatus.

DECLARE @tid INT
SET @tid = 2     -- our trace id
EXEC sp_trace_setstatus @tid, 1
GO

We can use this same function for stopping or deleting trace.

DECLARE @tid INT
SET @tid = 2     -- our trace id
EXEC sp_trace_setstatus @tid, 0 -- stops trace
GO
EXEC sp_trace_setstatus @tid, 2 -- deletes trace
GO

And at the end, for viewing *.trc file we’re using fn_trace_gettable function:

select * from ::fn_trace_gettable(N'c:\trace.trc',default)

Now let’s put this all together:

DECLARE @tid INT
EXEC sp_trace_create 
 @traceid = @tid OUTPUT, 
 @options = 2,
 @tracefile = N'c:\trace'

DECLARE @cid INT
SET @cid =1
WHILE(@cid < 65)
      BEGIN
	    EXEC sp_trace_setevent @tid, 10, @cid, 1
		EXEC sp_trace_setevent @tid, 12, @cid, 1
	    EXEC sp_trace_setevent @tid, 14, @cid, 1
		EXEC sp_trace_setevent @tid, 41, @cid, 1
		EXEC sp_trace_setevent @tid, 43, @cid, 1
		SET @cid += 1
      END

EXEC sp_trace_setfilter @tid, 1, 1, 6, N'%insert into %Users%'
EXEC sp_trace_setfilter @tid, 35, 0, 0, N'cms'

EXEC sp_trace_setstatus @tid, 1
GO

select * from ::fn_trace_gettable(N'c:\trace.trc',default)

/* stop and delete trace
EXEC sp_trace_setstatus 2, 0 -- stops trace
GO
EXEC sp_trace_setstatus 2, 2 -- deletes trace
GO
*/

I hope this will be helpful for someone.