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.
Trackbacks