June 4, 2002 at 1:14 am
Hi all,
I used SQL 2000 Profiler to trace certain table read access. I have set up event and filter, and it works well. In order to run the trace automatically, I convert the set up to script code using SQL 2000 Profiler->File->Script Trace->For SQL 2000 and create a stored procedure to run this script code. Unfortunatelly, the drive could not be filled up. I am wondering why? The following is my code:
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 2, N'\\PC723\C$\TbmasterTrace\TbmasterTry3', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 13, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 16, @on
exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 41, 16, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%select%tbmaster%'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQLAgent%'
set @intfilter = 100
exec sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
GO
The trace file can be created, but it is empty. Is there any one can help?
Many thanks
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
June 4, 2002 at 5:27 am
Try stoping and closing the trace
exec sp_trace_setstatus @TraceID, 0
exec sp_trace_setstatus @TraceID, 2
and see if the output doesn't dump then. I tested and code works fine for me. But I believe the dumps only occurr when so much data is in the buffer or when a stop is called. Also you stated you want to capture a specific table access but here
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'%select%tbmaster%'
you are using OR logic which means it will track everything ((app not like SQLAgent% AND app not like SQLProfiler AND ObjectID >= 100) OR (text is like %select%tbmaster%)). SO you catch everything for the one table and anything else where not the other parameters. Just wasn't sure if your logic was right.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 9, 2002 at 10:33 pm
Thanks Antares,
It works.....
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply